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$2 5,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" wrote in message
...
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$2 5,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