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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Lipetz - 11 Jan 2007 18:39 GMT

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

Reply to this Message

Dave Peterson - 11 Jan 2007 19:01 GMT

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

> 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
[quoted text clipped - 16 lines]
> Thanks,
> David

Signature

Dave Peterson

Reply to this Message

David Lipetz - 11 Jan 2007 20:28 GMT

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

> =isnumber(match(a1,Sheet2!a:a,0))
>
[quoted text clipped - 29 lines]
>> Thanks,
>> David

Reply to this Message

David Lipetz - 11 Jan 2007 19:06 GMT

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

> 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
[quoted text clipped - 16 lines]
> Thanks,
> David

Reply to this Message

Rate this thread: