Excel advanced multiple columns match

Question

I have 2 tables in Excel with several columns. I would like to look in one table for all rows that have 2 columns with the same value as the 2 other columns of the first table.

Example.

Table 1:

A1  A2  other columns....
02  AH
02L 
0A  0D
0D  0O
0O  1X
0WK 2K
1X  
02  2U
02L 2X
0A  3B

and table 2 :

A1  A2  other oclumns...
02  AH
02L AI
0A  AM
02  AP
02L 
0A  AP
2K  AR
2N  AS
1X  AS
02  AP

So for instance I would like to check in table 1 all the rows the have a match in table 2, meaning the same value in A1 and A2. The outcome can be just the word Match, to indicate that this line has a match in the second table.

Thank you for your help. Paul

Hi Tim, Well the outcome could be just a word saying this line has a match, that would be enough for me. Thx ! — Paul
Can you use a helper column? Can you add matching examples, so 02 AH will match with 02 AH in table 2? — Tim Wilkinson

Tim Wilkinson Tim Wilkinson · Accepted Answer · 2017-01-25T13:57:36

You could use an array formula:

{=IFERROR(VLOOKUP(A1&B1, Sheet2!A:A&Sheet2!B:B, 1, FALSE), "Not Found")}

Entered with SHIFT+CTRL+ENTER.

Alternatively use a helper column in the second Table

A   B   C
02  AH  =A1&B1
02L AI  =A2&B2
0A  AM  =A3&B3

Then you can use

=IFERROR(VLOOKUP(A1&B1, Sheet2!C:C, 1, FALSE), "Not Found")

To avoid using an array formula.

Excel advanced multiple columns match

1 Answers