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?

score 9 · Accepted Answer · edited May 19 '10 at 15:02

9

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

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

edited May 19 '10 at 15:02

BradC

39,306
13
73
89

answered May 19 '10 at 14:54

Mark Baker

209,507
32
346
385

1

So I'd just do =IF(ISERROR(); "not found"; "found") or something like that? – Svish May 19 '10 at 18:43
Yes. `=IF(ISERROR(Vlookup(...)),"not found","found")` keeping all the important bits inside the Vlookup function. – BradC May 19 '10 at 19:22
1

Or even =IF(ISERROR(Vlookup(...)),FALSE,TRUE) to give the True/False of your original request – Mark Baker May 19 '10 at 21:00
1

COUNTIF is a MUCH faster solution. – iDevlop May 19 '10 at 23:14

score 7 · Answer 2 · answered May 19 '10 at 14:59

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.

score 7 · Answer 3 · edited Jan 23 '14 at 19:16

7

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.

edited Jan 23 '14 at 19:16

vol7ron

40,809
21
119
172

answered May 19 '10 at 23:13

iDevlop

24,841
11
90
149

Could you give an easy example? 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? – Svish May 25 '10 at 17:37
1

The question was *is there a function that just returns true or false depending on if the value was found in a column or not?*. To which `COUNTIF` is the simplest answer. +1 – brettdj Jan 24 '14 at 02:58

score 1 · Answer 4 · edited Mar 22 '13 at 10:39

1

You can use:

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

edited Mar 22 '13 at 10:39

biddulph.r

5,226
3
32
47

answered Mar 22 '13 at 10:20

NickL

27
1

1

-1 for providing the same approach as an existing answer - 3 years afterwards. – brettdj Jan 24 '14 at 03:01

Dave Arkell · Answer 5 · 2010-05-19T16:24:39.717

1

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

edited May 19 '10 at 16:24

answered May 19 '10 at 14:58

Dave Arkell

3,920
2
22
27

except that my answer is for xl2003, with some additional information about xl2007 – Dave Arkell Jan 27 '14 at 15:38

score -1 · Answer 6 · answered Sep 11 '15 at 12:12

-1

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

answered Sep 11 '15 at 12:12

Mark M.

1

Excel: VLOOKUP that returns true or false?

6 Answers6