Okay, one for the Excel gurus. I’m trying to put a formula in on a spreadsheet to do the following:

If the <text comment> in column G contains XXXX or YYYY, then perform a vlookup (=VLOOKUP(P25, ‘Safety port group’!C:F, 4, FALSE), otherwise do nothing.

Not sure how I construct the formula though,

Any help appreciated, thanks!

if(A1=”XXXX”,vlookup….,if(A1=”YYYY”,vlookup,””))

Nested if, there is probably an or you could use

edit# the double quotes in the second false return a blank

=IF(ISERROR(FIND(“XXXXX”,G1)),IF(ISERROR(FIND(“YYYYY”,G1)),””,VLOOKUP(P25, ‘Safety port group’!C:F, 4, FALSE)),VLOOKUP(P25, ‘Safety port group’!C:F, 4, FALSE))

mike – I think he’s looking to test for the search string appearing in the target string, not matching it.

This formula above uses FIND. If it cant find the string it will return an error, it will then check for the second string and again if it cant find it will return an error. The ISERROR function converts that return error into a TRUE or FALSE statement from which you can trigger either the VLOOKUP function or a nothing “”

you guys are good!!!! Thanks for that. Stoner, you are the winner!

true stoner, my lazy excel is coming out…

no problem.

btw, quick tip, if you name your worksheets without spaces, then you dont need the enclosing apostrophes. Makes it a bit cleaner and simpler. Use an underscore if you like.

So ‘Safety port group’! becomes SafetyPortGroup!

EDIT: Oh and one more tip, referencing entire columns (C:F) in VLOOKUP uses quite a bit more memory than just the number of rows of your table. (e.g. C1:F100)

cheers, thanks for that