Results 1 to 6 of 6

How to compare/match 3 columns

This is a discussion on How to compare/match 3 columns within the Excel Questions forums, part of the Question Forums category; I've to compare two sheets with similar columns are : Sheet1: Column A (O/N), Column B ( P/N), Column C ...

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Hong Kong
    Posts
    38

    Default How to compare/match 3 columns

    I've to compare two sheets with similar columns are :
    Sheet1: Column A (O/N), Column B ( P/N), Column C (QTY), Column D (Date)
    Sheet2: Column A (QTY), Column B (O/N ), Column C (P/N)

    All Columns are in text format except Column D which is in Date mm/dd/yy).

    Here I have to compare these two sheets and return value of Coulmn D of sheet1 on Column E of sheet2 with following conditions:
    -If cln A of Sht1 equal to cln B of sht2 then go to copare cln B of sht1 with cln C of sht2;
    -if both match, then compare cln C of sht1 with cln A of sht2;
    -if these three column are matched, return value of cln D of sht1 on cln E of sht 2

    I've tried to use MATCH and INDEX formula, but fail at all. Should I use array formula to solve such conditions?

    Btw, anyone can help to post formula here?

    Tks a lot.

    Regards,
    CL

  2. #2
    Board Regular
    Join Date
    Aug 2005
    Posts
    108

    Default

    I think sumproduct formulas are way to go.

    http://www.mrexcel.com/board2/viewto...899&highlight=

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Hong Kong
    Posts
    38

    Default

    dtchan,

    Tks for your suggestion.
    But i still don't know how to make it.
    Could you post formula with example sheet here?

    Regards,
    CL

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,188

    Default Re: How to compare/match 3 columns

    Quote Originally Posted by clwong
    I've to compare two sheets with similar columns are :
    Sheet1: Column A (O/N), Column B ( P/N), Column C (QTY), Column D (Date)
    Sheet2: Column A (QTY), Column B (O/N ), Column C (P/N)

    All Columns are in text format except Column D which is in Date mm/dd/yy).

    Here I have to compare these two sheets and return value of Coulmn D of sheet1 on Column E of sheet2 with following conditions:
    -If cln A of Sht1 equal to cln B of sht2 then go to copare cln B of sht1 with cln C of sht2;
    -if both match, then compare cln C of sht1 with cln A of sht2;
    -if these three column are matched, return value of cln D of sht1 on cln E of sht 2

    I've tried to use MATCH and INDEX formula, but fail at all. Should I use array formula to solve such conditions?

    Btw, anyone can help to post formula here?

    Tks a lot.

    Regards,
    CL
    Search for multikey lookup where an additional column is used to concatenate relevant columns.

  5. #5
    New Member
    Join Date
    Mar 2002
    Location
    Hong Kong
    Posts
    38

    Default

    Hi, Aladin,

    Tks for your hits too.
    Sorry, as I don't know what "Search for multikey lookup where an additional column is used to concatenate relevant columns." mean, could you give me more hits or a real example?

    Tks in adv.

    Yours,
    CL

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,188

    Default

    Quote Originally Posted by clwong
    Hi, Aladin,

    Tks for your hits too.
    Sorry, as I don't know what "Search for multikey lookup where an additional column is used to concatenate relevant columns." mean, could you give me more hits or a real example?

    Tks in adv.

    Yours,
    CL
    See my post in: http://www.mrexcel.com/board2/viewtopic.php?t=148849

    Othwerwise, provide a 5-row sample from each sheet.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com