# Thread: Matching Two Table (Excel 2003)

1. ## Matching Two Table (Excel 2003)

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

2. ## Re: Matching Two Table (Excel 2003)

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(MA TCH(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.

3. ## Re: Matching Two Table (Excel 2003)

Hans

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

Thanks

regards, francis

4. ## Re: Matching Two Table (Excel 2003)

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:

<table border=1><td> </td><td>P</td><td>Q</td><td>1+P+2*Q</td><td>Both match</td><td align=right>0</td><td align=right>0</td><td align=right>1</td><td>First match</td><td align=right>1</td><td align=right>0</td><td align=right>2</td><td>Second match</td><td align=right>0</td><td align=right>1</td><td align=right>3</td><td>No match</td><td align=right>1</td><td align=right>1</td><td align=right>4</td></table>
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.

5. ## Re: Matching Two Table (Excel 2003)

Hans

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

regards, francis

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•