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

User · Answer

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

User · Answer

You can use    IF ISERROR VLOOKUP lookup value table array column no FALSE    FALSE   TRUE

User · Answer

You could wrap your VLOOKUP   in an IFERROR    Edit  before Excel 2007  use  IF ISERROR

User · Answer

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     gt 0 LOOKUP      Not found      A bit of Googling will bring you tons of examples

User · Answer

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

User · Answer

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

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

Examples related to excel

Examples related to excel-formula

Examples related to excel-2003