Excel: VLOOKUP that returns true or false?

Question

In Excel we have the VLOOKUP function that looks for a value in a column in a table and then returns a value from a given column in that table if it finds something. If it doesn't, it produces an error.

Is there a function that just returns true or false depending on if the value was found in a column or not?

Answer 1

You could wrap your VLOOKUP() in an IFERROR()

Edit: before Excel 2007, use =IF(ISERROR()...)

Answer 2

You still have to wrap it in an ISERROR, but you could use MATCH() instead of VLOOKUP():

Returns the relative position of an item in an array that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself.

Here's a complete example, assuming you're looking for the word "key" in a range of cells:

=IF(ISERROR(MATCH("key",A5:A16,FALSE)),"missing","found")

The FALSE is necessary to force an exact match, otherwise it will look for the closest value.