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