Geek Answers Handbook

Excel: VLOOKUP that returns true or false?

In Excel, we have a function VLOOKUPthat searches for a value in a column in a table, and then returns the value from a given column in this table if it finds something. If this is not the case, it will result in an error.

Is there a function that returns true or false depending on whether the value was found in the column or not?

+5
source share
6 answers

You can wrap your VLOOKUP () in IFERROR ()

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

+10
source

You still have to wrap it in ISERROR, but you can use MATCH()instead VLOOKUP():

, . MATCH LOOKUP , , .

, , "" :

=IF(ISERROR(MATCH("key",A5:A16,FALSE)),"missing","found")

FALSE , .

+6

COUNTIF! , .

:

, A1 1, B1 C, 2. ?

, B1 C, A1 , 0. : =COUNTIF($C$1:$C$15,B1), : C1:C15 B1.

COUNTIF VLOOKUP IF, , 2 + ISNA. IF(COUNTIF(..)>0,LOOKUP(..),"Not found")

Googling .

+6

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

Excel 2007 IfError, vlookup , 2003...

+1

:

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

ISNA - . . , , .

= ( ($ A2, 1 $A: $D, 2, FALSE))

-1

More articles:


All Articles