Matching Two Table (Excel 2003)

Topic

New Reply

Hi All

I have two sheets. I need to match column B with column D on sheet 1,
and at the same time, I need to match also column B of sheet 1 with column B of sheet 2 both using column A as a common identifier.

If column B match with column D on sheet 1 and column B of sheet 1 match with column B of sheet 2, return ” match ”

If column B match with column D on sheet 1 but column B of sheet 1 does not match with column B of sheet 2, return ” name match ”

if column B does not match with column D on sheet 1 but column B of sheet 1 match with column B of sheet 2, return ” symbol match ”

if all of the above does not match, return ” Unmatch ”

TIA

Regards, francis

Reply | Quote

Replies

Let’s say that your data start in row 2. You could use a formula like this:

=CHOOSE(1+ISNA(MATCH(B2,$D$2:$D$1000,0))+2*ISNA(MATCH(B2,Sheet2!$B$2:$B$1000,0)),”Match”,”Symbol match”,”Name match”,”No match”)

for row 2, and fill down. Change the upper bound 1000 as needed.

Reply | Quote

Hans

Excellent ! Thank you. I was trying to use Index / Match but without success.

Would you explain about this formula if it not too much to ask for.

Thanks

regards, francis

Reply | Quote

The expression ISNA(MATCH(B2,$D$2:$D$1000,0)) returns TRUE = 1 if there is no match for B2 in column D on the same sheet, and FALSE = 0 if there is a match. Let’sabbreviate this expression as P.

The expression ISNA(MATCH(B2,Sheet2!$B$2:$B$1000,0)) returns TRUE = 1 if there is no match for B2 in column B on Sheet2, and FALSE = 0 if there is a match. We’ll abbreviate this one as Q.

The formula computes 1+P+2*Q. The result is as follows:

	P	Q	1+P+2*Q
Both match	0	0	1
First match	1	0	2
Second match	0	1	3
No match	1	1	4

The CHOOSE function looks at the first argument, and if this is 1 it returns the first argument after that, if it is 2 the second argument after it etc.

Reply | Quote

Hans

Thanks for the explanation. I will need sometime to digest this. This will definitely goes into my library

regards, francis

Reply | Quote