+ Reply to Thread
Results 1 to 6 of 6

Compare 2 columns from two different tables

  1. #1
    Registered User
    Join Date
    01-11-2019
    Location
    Austin, TX
    MS-Off Ver
    no idea
    Posts
    8

    Compare 2 columns from two different tables

    I need an excel guru to see if they can figure this out:.

    I have table 1 and table 2
    Column A in both tables are vendor numbers
    Column B in both tables are invoice numbers

    I need a formula to compare column A of both tables. If it find a match within the column, then I want it to compare column B.
    If it finds a match for both then it needs to produce something like true, false, whatever.

    But I need both columns to match, not just if column A matches column A but not column B. If vendor number matches from one table column to the other table column, then I need to know if the invoice number matches in both columns. If both tables have that data I need a "yes". If one table is missing the data I need a "no".

    Super simple example:

    Table 1:
    Column A: Column B: Column C:
    Vendor #. Invoice #. Data Match
    112. 05871. NO
    111. 25034. YES

    Table 2:
    Column A: Column B:
    Vendor #. Invoice #
    111. 25034
    112. 87150

    The ideal formula will insert answer into Column C in table 1
    Does this make sense? What formula can I use? (V-lookup is great except I’m only comparing one column of data... I need in the case of 2 columns)...

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    3,402

    Re: Compare 2 columns from two different tables

    Try using MATCH function.
    MATCH(LookupValue, LookupColumn, 0) will return row (index) # if exact match is found.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    4,834

    Re: Compare 2 columns from two different tables

    Hello and welcome to the forum.

    It sounds like you can use COUNTIFS for this.

    Something along the lines of this in C2 of Sheet1:

    =IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,B2),"Yes","No")

    Where Table 1 is in Sheet1 and Table 2 is in Sheet2.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2007
    Posts
    8,643

    Re: Compare 2 columns from two different tables

    What about >> =IFERROR(IF(MATCH(A2&"|"&B2,'Table 2'!$A$2:$A$25&"|"&'Table 2'!$B$2:$B$25,0),"Yes",""),"No") >> in C2 of Table 1 copied down

    IMPORTANT
    • This is an array formula
    • Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
    • If entered correctly, the formula will be enclosed in {brackets}
    • Do not enter the {brackets} manually
    HTH
    Regards, Jeff

  5. #5
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    3,402

    Re: Compare 2 columns from two different tables

    Ah! I had missed the column A criteria.

    You can use below then.
    =AGGREGATE(14,6,('Table 2'!$A$2:$A$25=A2)*('Table 2'!$B$2:$B$25=B2),1)

    It will return 0 if no match is found, and 1 if match found.

  6. #6
    Registered User
    Join Date
    01-11-2019
    Location
    Austin, TX
    MS-Off Ver
    no idea
    Posts
    8

    Re: Compare 2 columns from two different tables

    Thanks everyone. I will try them all and see what happens. I was sure a sumifs would work, except I didn't want a sum. lol Hadn't thought of these other options. I'll reply back if I get it correctly!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1