Vlookup Displays Blank

J

Jani

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
 
P

Peo Sjoblom

=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
 
B

bpeltzer

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.
 
J

Jani

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???
 
P

Peo Sjoblom

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
 
J

Jani

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?
 
P

Peo Sjoblom

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

[email protected]

remove NOSPAM from the email address


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 
P

Peo Sjoblom

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
 
Top