CODEWITHSUNDEEP
excelexcel-formulaexcel-2003
Svish
Svish

Reputation: 158181

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?

Upvotes: 6

Views: 115357

Answers (6)

Mark M.
Mark M.

Reputation: 21

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

Upvotes: 1

iDevlop
iDevlop

Reputation: 25272

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.

Upvotes: 8

NickL
NickL

Reputation: 27

You can use:

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

Upvotes: 1

Dave Arkell
Dave Arkell

Reputation: 3980

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

Upvotes: 1

Mark Baker
Mark Baker

Reputation: 212452

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

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

Upvotes: 8

BradC
BradC

Reputation: 39986

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.

Upvotes: 7

Related Questions