[excel] Excel: VLOOKUP that returns true or false?

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?

We've always used an

if(iserror(vlookup,"n/a",vlookup))

Excel 2007 introduced IfError which allows you to do the vlookup and add output in case of error, but that doesn't help you with 2003...

ISNA is the best function to use. I just did. I wanted all cells whose value was NOT in an array to conditionally format to a certain color.

=ISNA(VLOOKUP($A2,Sheet1!$A:$D,2,FALSE))

You can use:

=IF(ISERROR(VLOOKUP(lookup value,table array,column no,FALSE)),"FALSE","TRUE")

Just use a COUNTIF ! Much faster to write and calculate than the other suggestions.

EDIT:

Say you cell A1 should be 1 if the value of B1 is found in column C and otherwise it should be 2. How would you do that?

I would say if the value of B1 is found in column C, then A1 will be positive, otherwise it will be 0. Thats easily done with formula: =COUNTIF($C$1:$C$15,B1), which means: count the cells in range C1:C15 which are equal to B1.

You can combine COUNTIF with VLOOKUP and IF, and that's MUCH faster than using 2 lookups + ISNA. IF(COUNTIF(..)>0,LOOKUP(..),"Not found")

A bit of Googling will bring you tons of examples.

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.