VLOOKUP query

[hatstand]

I have a VLOOKUP formula that when a recognised entry is made. A cell is populated with the answer. My problem is that some entries are made that are not in the table so the answer is, what ever the closest match is. Can the formula include something that gives the answer “not on list” when a non-table entry is made.

 

[Andrew Poulsom]

For an exact match you need to set VLOOKUP's fourth (Range_lookup) to FALSE. You can use ISNA to check and return some text, eg:

=IF(ISNA(VLOOKUP(Ref,Table,2,FALSE)),"not on list",VLOOKUP(Ref,Table,2,FALSE))

 

[texasalynn]

care to post the formula you have

 

[barry houdini]

If you use 0 as the 4th argument you only get exact matches or #N/A, i.e.

=VLOOKUP(A1,B2:C3,2,0)

 

[Joe4]

Make sure that you have the last argument in the VLOOKUP function set to FALSE or 0 so that it does not return approximate matches. It will then return "#N/A" for all non-matches.

If you want to return something else, use this format:
=IF(ISNA(VLOOKUP(...)),"not on list","VLOOKUP(...))

 

[hatstand]

A big thank you

A big thanks to everyone, never had so many answers so quick. It's working perfectly.