MATCH, VLOOKUP? How to get T/F Result?

[David Lipetz]

In one sheet of a multi-sheet workbook, I've got a column of customer
numbers. In a separate sheet, I've got a column of customer numbers which
represents a subset of the whole customer list.

I need to be able to add an alpha code (single letter) in a separate column
on the first sheet if the customer number on that row is contained in the
subset list on the second sheet.

Logically, Excel needs to look at the customer nimber on sheet 1 and try to
find it on sheet 2. If it does find it, place an "A" in the cell, if it does
not find it, leave the cell blank.

I've experimented with VLOOKUP and MATCH, but both return #N/A when the cell
is not found. I also tried creating an IF statement incorporating ISERROR or
ISNA, but neither are working for me.

How should I go about this formula?

Thanks,
David

 

[Dave Peterson]

=isnumber(match(a1,Sheet2!a:a,0))

will return True or false if A1 is in Sheet2 in column A.

=if(isnumber(match(a1,Sheet2!a:a,0)),"A","")

will put in an A or make it look blank.

 

[David Lipetz]

Never mind, I think. The customer numbers in the second sheet were stored as
text, thus my formula was not working.

After converting to value, the following formula appears to work:

=IF(ISNA(MATCH(B4,SHEET2!$A$2:$A$48,0)),"","A")

 

[David Lipetz]

Thanks Dave.

ISNUMBER will work too and probably better than what I ended up with:

=IF(ISNA(MATCH(B4,Sheet2!$A$2:$A$48,0)),"","A")