Results 1 to 5 of 5
Thread: Matching Two Table (Excel 2003)

20080517, 22:10 #1
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
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, francisHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20080517, 22:28 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20080517, 23:01 #3
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Matching Two Table (Excel 2003)
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, francisHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20080517, 23:29 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20080517, 23:42 #5
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
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, francisHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array