Vlookup Displays Blank

I can't figure out where have I gone wrong with this formula:

=IF(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE),"Ops Plant
Engineer - "&GlobalOpsInput!$F$21)

What I am trying to do is if the text is found in B25, then look at RefTable
and pull in the data in the second column (which is a formula: ="Director
Customer Service - "&RefTables!G121), if not found, then insert "Ops Plant
Engineer" with the data in cell F21. If the data is found, the cell with the
formula is blank, if it is not found then there is a #VALUE error. Even if I
type something in the RefTable second column, the formula still returns a
blank.

Thanks for your help! jms

=IF(ISNA(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE)),"Ops
Plant
Engineer -
"&GlobalOpsInput!$F$21,VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE))



--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Jani" <Jani@discussions.microsoft.com> wrote in message
news:5778B306-6FE8-4362-82B1-2F60DF7D4090@microsoft.com...
>I can't figure out where have I gone wrong with this formula:
>
> =IF(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE),"Ops
> Plant
> Engineer - "&GlobalOpsInput!$F$21)
>
> What I am trying to do is if the text is found in B25, then look at
> RefTable
> and pull in the data in the second column (which is a formula: ="Director
> Customer Service - "&RefTables!G121), if not found, then insert "Ops Plant
> Engineer" with the data in cell F21. If the data is found, the cell with
> the
> formula is blank, if it is not found then there is a #VALUE error. Even if
> I
> type something in the RefTable second column, the formula still returns a
> blank.
>
> Thanks for your help! jms

The condition you've set in the test isn't whether a match is found, but
rather the result of the vlookup. Try
=if(isna(vlookup(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE)),"Ops
Plant Engineer -
"&GlobalOpsInput!$F$21,vlookup(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE))
In words: if the vlookup fails (returns NA), set the result to "Ops Plant
Engineer..."; otherwise use the result of the vlookup.

"Jani" wrote:

> I can't figure out where have I gone wrong with this formula:
>
> =IF(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE),"Ops Plant
> Engineer - "&GlobalOpsInput!$F$21)
>
> What I am trying to do is if the text is found in B25, then look at RefTable
> and pull in the data in the second column (which is a formula: ="Director
> Customer Service - "&RefTables!G121), if not found, then insert "Ops Plant
> Engineer" with the data in cell F21. If the data is found, the cell with the
> formula is blank, if it is not found then there is a #VALUE error. Even if I
> type something in the RefTable second column, the formula still returns a
> blank.
>
> Thanks for your help! jms

You two were sure quick and have solved most of the problem, but I am still
getting a #Value error when it doesn't find the condition when it should be
entering the "Ops Plant Engineer -" with the value from GlobalOpsInput!$F$21.
Any other thoughts???

"bpeltzer" wrote:

> The condition you've set in the test isn't whether a match is found, but
> rather the result of the vlookup. Try
> =if(isna(vlookup(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE)),"Ops
> Plant Engineer -
> "&GlobalOpsInput!$F$21,vlookup(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE))
> In words: if the vlookup fails (returns NA), set the result to "Ops Plant
> Engineer..."; otherwise use the result of the vlookup.
>
> "Jani" wrote:
>
> > I can't figure out where have I gone wrong with this formula:
> >
> > =IF(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE),"Ops Plant
> > Engineer - "&GlobalOpsInput!$F$21)
> >
> > What I am trying to do is if the text is found in B25, then look at RefTable
> > and pull in the data in the second column (which is a formula: ="Director
> > Customer Service - "&RefTables!G121), if not found, then insert "Ops Plant
> > Engineer" with the data in cell F21. If the data is found, the cell with the
> > formula is blank, if it is not found then there is a #VALUE error. Even if I
> > type something in the RefTable second column, the formula still returns a
> > blank.
> >
> > Thanks for your help! jms

Make sure the value isn't

"Ops Plant Engineer "-GlobalOpsInput!$F$21

that would create a value error so you might want to take a look at that
part and type it in again an make sure it looks like

"Ops Plant Engineer -"&GlobalOpsInput!$F$21

the only other way you can get a value error is if you have the same error
in a cell that is involved


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com




"Jani" <Jani@discussions.microsoft.com> wrote in message
news:DFA0C80D-D8A9-4F4B-BE79-35786D5F940C@microsoft.com...
> You two were sure quick and have solved most of the problem, but I am
> still
> getting a #Value error when it doesn't find the condition when it should
> be
> entering the "Ops Plant Engineer -" with the value from
> GlobalOpsInput!$F$21.
> Any other thoughts???
>
> "bpeltzer" wrote:
>
>> The condition you've set in the test isn't whether a match is found, but
>> rather the result of the vlookup. Try
>> =if(isna(vlookup(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE)),"Ops
>> Plant Engineer -
>> "&GlobalOpsInput!$F$21,vlookup(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE))
>> In words: if the vlookup fails (returns NA), set the result to "Ops Plant
>> Engineer..."; otherwise use the result of the vlookup.
>>
>> "Jani" wrote:
>>
>> > I can't figure out where have I gone wrong with this formula:
>> >
>> > =IF(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE),"Ops
>> > Plant
>> > Engineer - "&GlobalOpsInput!$F$21)
>> >
>> > What I am trying to do is if the text is found in B25, then look at
>> > RefTable
>> > and pull in the data in the second column (which is a formula:
>> > ="Director
>> > Customer Service - "&RefTables!G121), if not found, then insert "Ops
>> > Plant
>> > Engineer" with the data in cell F21. If the data is found, the cell
>> > with the
>> > formula is blank, if it is not found then there is a #VALUE error. Even
>> > if I
>> > type something in the RefTable second column, the formula still returns
>> > a
>> > blank.
>> >
>> > Thanks for your help! jms

Still no luck... this is exactly what the formula is:
=IF(ISNA(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE)),"Ops
Plant Engineer
-"&GlobalOpsInput!$F$21,VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE))

I have deleted the contents of Cell F21 and made sure is is 'blank' and have
tried the formula again but still getting the #Value error.

Any further thoughts?



"Peo Sjoblom" wrote:

> Make sure the value isn't
>
> "Ops Plant Engineer "-GlobalOpsInput!$F$21
>
> that would create a value error so you might want to take a look at that
> part and type it in again an make sure it looks like
>
> "Ops Plant Engineer -"&GlobalOpsInput!$F$21
>
> the only other way you can get a value error is if you have the same error
> in a cell that is involved
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
>
>
> "Jani" <Jani@discussions.microsoft.com> wrote in message
> news:DFA0C80D-D8A9-4F4B-BE79-35786D5F940C@microsoft.com...
> > You two were sure quick and have solved most of the problem, but I am
> > still
> > getting a #Value error when it doesn't find the condition when it should
> > be
> > entering the "Ops Plant Engineer -" with the value from
> > GlobalOpsInput!$F$21.
> > Any other thoughts???
> >
> > "bpeltzer" wrote:
> >
> >> The condition you've set in the test isn't whether a match is found, but
> >> rather the result of the vlookup. Try
> >> =if(isna(vlookup(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE)),"Ops
> >> Plant Engineer -
> >> "&GlobalOpsInput!$F$21,vlookup(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE))
> >> In words: if the vlookup fails (returns NA), set the result to "Ops Plant
> >> Engineer..."; otherwise use the result of the vlookup.
> >>
> >> "Jani" wrote:
> >>
> >> > I can't figure out where have I gone wrong with this formula:
> >> >
> >> > =IF(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE),"Ops
> >> > Plant
> >> > Engineer - "&GlobalOpsInput!$F$21)
> >> >
> >> > What I am trying to do is if the text is found in B25, then look at
> >> > RefTable
> >> > and pull in the data in the second column (which is a formula:
> >> > ="Director
> >> > Customer Service - "&RefTables!G121), if not found, then insert "Ops
> >> > Plant
> >> > Engineer" with the data in cell F21. If the data is found, the cell
> >> > with the
> >> > formula is blank, if it is not found then there is a #VALUE error. Even
> >> > if I
> >> > type something in the RefTable second column, the formula still returns
> >> > a
> >> > blank.
> >> >
> >> > Thanks for your help! jms
>
>
>

So if I understand you correctly if there's a match the formula works but if
not it will return the error?
Can you copy the workbook, remove all sensitive data and tables etc, make
sure you still get the error then email the workbook to

terre_s@NOSPAMeyahoo.com

remove NOSPAM from the email address


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Jani" <Jani@discussions.microsoft.com> wrote in message
news:3EE3055D-5C3B-43AE-B4FE-7D3521BD258E@microsoft.com...
> Still no luck... this is exactly what the formula is:
> =IF(ISNA(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE)),"Ops
> Plant Engineer
> -"&GlobalOpsInput!$F$21,VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE))
>
> I have deleted the contents of Cell F21 and made sure is is 'blank' and
> have
> tried the formula again but still getting the #Value error.
>
> Any further thoughts?
>
>
>
> "Peo Sjoblom" wrote:
>
>> Make sure the value isn't
>>
>> "Ops Plant Engineer "-GlobalOpsInput!$F$21
>>
>> that would create a value error so you might want to take a look at that
>> part and type it in again an make sure it looks like
>>
>> "Ops Plant Engineer -"&GlobalOpsInput!$F$21
>>
>> the only other way you can get a value error is if you have the same
>> error
>> in a cell that is involved
>>
>>
>> --
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>> http://nwexcelsolutions.com
>>
>>
>>
>>
>> "Jani" <Jani@discussions.microsoft.com> wrote in message
>> news:DFA0C80D-D8A9-4F4B-BE79-35786D5F940C@microsoft.com...
>> > You two were sure quick and have solved most of the problem, but I am
>> > still
>> > getting a #Value error when it doesn't find the condition when it
>> > should
>> > be
>> > entering the "Ops Plant Engineer -" with the value from
>> > GlobalOpsInput!$F$21.
>> > Any other thoughts???
>> >
>> > "bpeltzer" wrote:
>> >
>> >> The condition you've set in the test isn't whether a match is found,
>> >> but
>> >> rather the result of the vlookup. Try
>> >> =if(isna(vlookup(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE)),"Ops
>> >> Plant Engineer -
>> >> "&GlobalOpsInput!$F$21,vlookup(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE))
>> >> In words: if the vlookup fails (returns NA), set the result to "Ops
>> >> Plant
>> >> Engineer..."; otherwise use the result of the vlookup.
>> >>
>> >> "Jani" wrote:
>> >>
>> >> > I can't figure out where have I gone wrong with this formula:
>> >> >
>> >> > =IF(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE),"Ops
>> >> > Plant
>> >> > Engineer - "&GlobalOpsInput!$F$21)
>> >> >
>> >> > What I am trying to do is if the text is found in B25, then look at
>> >> > RefTable
>> >> > and pull in the data in the second column (which is a formula:
>> >> > ="Director
>> >> > Customer Service - "&RefTables!G121), if not found, then insert "Ops
>> >> > Plant
>> >> > Engineer" with the data in cell F21. If the data is found, the cell
>> >> > with the
>> >> > formula is blank, if it is not found then there is a #VALUE error.
>> >> > Even
>> >> > if I
>> >> > type something in the RefTable second column, the formula still
>> >> > returns
>> >> > a
>> >> > blank.
>> >> >
>> >> > Thanks for your help! jms
>>
>>
>>

The error was due to that "transition formula evaluation" was checked under
tools>options>transition.

This sometimes happen with minor things that are incompatible with regards
to Lotus 123 and Excel
Since this was a large and rather complicated spreadsheet with lots of
interdependent formulas
I won't have the time to audit it to pinpoint the exact formula that was the
root of the error but since Jani had a lot of text numbers with trailing
spaces I am pretty sure somewhere there is the reason.
As an example where this option checked returns an error is

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)

will return the last day of today's month but if you select "transition
formula evaluation"
it will return #NUM! and of course any dependent formula will also return
the error


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com




"Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
news:e7dAg9lXGHA.508@TK2MSFTNGP02.phx.gbl...
> So if I understand you correctly if there's a match the formula works but
> if not it will return the error?
> Can you copy the workbook, remove all sensitive data and tables etc, make
> sure you still get the error then email the workbook to
>
> terre_s@NOSPAMeyahoo.com
>
> remove NOSPAM from the email address
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
> "Jani" <Jani@discussions.microsoft.com> wrote in message
> news:3EE3055D-5C3B-43AE-B4FE-7D3521BD258E@microsoft.com...
>> Still no luck... this is exactly what the formula is:
>> =IF(ISNA(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE)),"Ops
>> Plant Engineer
>> -"&GlobalOpsInput!$F$21,VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE))
>>
>> I have deleted the contents of Cell F21 and made sure is is 'blank' and
>> have
>> tried the formula again but still getting the #Value error.
>>
>> Any further thoughts?
>>
>>
>>
>> "Peo Sjoblom" wrote:
>>
>>> Make sure the value isn't
>>>
>>> "Ops Plant Engineer "-GlobalOpsInput!$F$21
>>>
>>> that would create a value error so you might want to take a look at that
>>> part and type it in again an make sure it looks like
>>>
>>> "Ops Plant Engineer -"&GlobalOpsInput!$F$21
>>>
>>> the only other way you can get a value error is if you have the same
>>> error
>>> in a cell that is involved
>>>
>>>
>>> --
>>>
>>> Regards,
>>>
>>> Peo Sjoblom
>>>
>>> http://nwexcelsolutions.com
>>>
>>>
>>>
>>>
>>> "Jani" <Jani@discussions.microsoft.com> wrote in message
>>> news:DFA0C80D-D8A9-4F4B-BE79-35786D5F940C@microsoft.com...
>>> > You two were sure quick and have solved most of the problem, but I am
>>> > still
>>> > getting a #Value error when it doesn't find the condition when it
>>> > should
>>> > be
>>> > entering the "Ops Plant Engineer -" with the value from
>>> > GlobalOpsInput!$F$21.
>>> > Any other thoughts???
>>> >
>>> > "bpeltzer" wrote:
>>> >
>>> >> The condition you've set in the test isn't whether a match is found,
>>> >> but
>>> >> rather the result of the vlookup. Try
>>> >> =if(isna(vlookup(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE)),"Ops
>>> >> Plant Engineer -
>>> >> "&GlobalOpsInput!$F$21,vlookup(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE))
>>> >> In words: if the vlookup fails (returns NA), set the result to "Ops
>>> >> Plant
>>> >> Engineer..."; otherwise use the result of the vlookup.
>>> >>
>>> >> "Jani" wrote:
>>> >>
>>> >> > I can't figure out where have I gone wrong with this formula:
>>> >> >
>>> >> > =IF(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE),"Ops
>>> >> > Plant
>>> >> > Engineer - "&GlobalOpsInput!$F$21)
>>> >> >
>>> >> > What I am trying to do is if the text is found in B25, then look at
>>> >> > RefTable
>>> >> > and pull in the data in the second column (which is a formula:
>>> >> > ="Director
>>> >> > Customer Service - "&RefTables!G121), if not found, then insert
>>> >> > "Ops
>>> >> > Plant
>>> >> > Engineer" with the data in cell F21. If the data is found, the cell
>>> >> > with the
>>> >> > formula is blank, if it is not found then there is a #VALUE error.
>>> >> > Even
>>> >> > if I
>>> >> > type something in the RefTable second column, the formula still
>>> >> > returns
>>> >> > a
>>> >> > blank.
>>> >> >
>>> >> > Thanks for your help! jms
>>>
>>>
>>>
>
>

Thanks, Peo, with your patience in getting me on the right track! jms

"Peo Sjoblom" wrote:

> So if I understand you correctly if there's a match the formula works but if
> not it will return the error?
> Can you copy the workbook, remove all sensitive data and tables etc, make
> sure you still get the error then email the workbook to
>
> terre_s@NOSPAMeyahoo.com
>
> remove NOSPAM from the email address
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
> "Jani" <Jani@discussions.microsoft.com> wrote in message
> news:3EE3055D-5C3B-43AE-B4FE-7D3521BD258E@microsoft.com...
> > Still no luck... this is exactly what the formula is:
> > =IF(ISNA(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE)),"Ops
> > Plant Engineer
> > -"&GlobalOpsInput!$F$21,VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE))
> >
> > I have deleted the contents of Cell F21 and made sure is is 'blank' and
> > have
> > tried the formula again but still getting the #Value error.
> >
> > Any further thoughts?
> >
> >
> >
> > "Peo Sjoblom" wrote:
> >
> >> Make sure the value isn't
> >>
> >> "Ops Plant Engineer "-GlobalOpsInput!$F$21
> >>
> >> that would create a value error so you might want to take a look at that
> >> part and type it in again an make sure it looks like
> >>
> >> "Ops Plant Engineer -"&GlobalOpsInput!$F$21
> >>
> >> the only other way you can get a value error is if you have the same
> >> error
> >> in a cell that is involved
> >>
> >>
> >> --
> >>
> >> Regards,
> >>
> >> Peo Sjoblom
> >>
> >> http://nwexcelsolutions.com
> >>
> >>
> >>
> >>
> >> "Jani" <Jani@discussions.microsoft.com> wrote in message
> >> news:DFA0C80D-D8A9-4F4B-BE79-35786D5F940C@microsoft.com...
> >> > You two were sure quick and have solved most of the problem, but I am
> >> > still
> >> > getting a #Value error when it doesn't find the condition when it
> >> > should
> >> > be
> >> > entering the "Ops Plant Engineer -" with the value from
> >> > GlobalOpsInput!$F$21.
> >> > Any other thoughts???
> >> >
> >> > "bpeltzer" wrote:
> >> >
> >> >> The condition you've set in the test isn't whether a match is found,
> >> >> but
> >> >> rather the result of the vlookup. Try
> >> >> =if(isna(vlookup(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE)),"Ops
> >> >> Plant Engineer -
> >> >> "&GlobalOpsInput!$F$21,vlookup(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE))
> >> >> In words: if the vlookup fails (returns NA), set the result to "Ops
> >> >> Plant
> >> >> Engineer..."; otherwise use the result of the vlookup.
> >> >>
> >> >> "Jani" wrote:
> >> >>
> >> >> > I can't figure out where have I gone wrong with this formula:
> >> >> >
> >> >> > =IF(VLOOKUP(GlobalOpsInput!$B$25,RefTables!$A$130:$B$133,2,FALSE),"Ops
> >> >> > Plant
> >> >> > Engineer - "&GlobalOpsInput!$F$21)
> >> >> >
> >> >> > What I am trying to do is if the text is found in B25, then look at
> >> >> > RefTable
> >> >> > and pull in the data in the second column (which is a formula:
> >> >> > ="Director
> >> >> > Customer Service - "&RefTables!G121), if not found, then insert "Ops
> >> >> > Plant
> >> >> > Engineer" with the data in cell F21. If the data is found, the cell
> >> >> > with the
> >> >> > formula is blank, if it is not found then there is a #VALUE error.
> >> >> > Even
> >> >> > if I
> >> >> > type something in the RefTable second column, the formula still
> >> >> > returns
> >> >> > a
> >> >> > blank.
> >> >> >
> >> >> > Thanks for your help! jms
> >>
> >>
> >>
>
>
>