Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    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, francis
    Hope 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

  2. #2
    Plutonium Lounger
    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.

  3. #3
    3 Star Lounger
    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, francis
    Hope 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

  4. #4
    Plutonium Lounger
    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.

  5. #5
    3 Star Lounger
    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, francis
    Hope 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

Posting Permissions

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