Confusing Vlookup

[JadonR]

I have the following vlookup. However, when it doesn't match any of the values from column D, then I want it to return "EACH". I tried changing FALSE to EACH but that doesn't work.

[B]=VLOOKUP(A11,’Product Database’!C2:D7,2,FALSE)[/B]

 

[Jerry Sullivan]

If you have xl2007 or later you can use...

=IFERROR(VLOOKUP(A11,'Product Database'!C2:D7,2,FALSE),"EACH")

The last parameter of the VLOOKUP function is used to specify:
FALSE means find exact match
TRUE means find an approximate match

...when it doesn't match any of the values from column D...

I'm guessing this was a typo, but just in case....
Note that VLOOKUP looks for matches in the leftmost column of the lookup range.
So in your example formula it will look in Col C for a match and if found, return the value in Col D.

 

[JadonR]

Cool, thanks. You are correct, I did mean column C. Is there any formula that will work for pre-2007 or Open Office?

 

[Jonmo1]

For pre 2007...

=IF(ISNA(VLOOKUP(A11,’Product Database’!C2:D7,2,FALSE)),"EACH",VLOOKUP(A11,’Product Database’!C2:D7,2,FALSE))


Hope that helps

 

[JadonR]

Thanks JS411, works great!

@Jonmo1, for some reason I get a #NAME! error when I use yours. Any ideas?

 

[Jonmo1]

Thanks JS411, works great!

@Jonmo1, for some reason I get a #NAME! error when I use yours. Any ideas?

Somehting was wrong with the apostrophe's when I copied your formula into mine...

try

=IF(ISNA(VLOOKUP(A11,'Product Database'!C2:D7,2,FALSE)),"EACH",VLOOKUP(A11,'Product Database'!C2:D7,2,FALSE))

 

[JadonR]

Thanks works perfectly!!!