# Compare 2 columns from two different tables

1. ## 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. ## 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.

3. ## 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. ## 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

5. ## 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. ## 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!

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