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?

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

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

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.

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.

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...

You can use:

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

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))