When using VLOOKUP with data that isn’t made up of unique values you need to combine values to create a unique reference
You can either create additional columns in both data sources and populate with & or CONCATENATE, or you can combine the cell grouping into the VLOOKUP function.
Example – combine Sheet1 columns A and B to create a unique reference, then use VLOOKUP to search for same in Sheet2 (where an additional column has been created and populated with & or CONCATENATE as appropriate)
=IF(ISNA(VLOOKUP(A1&B1,Sheet2!$A:$A,1,FALSE)),"not found","found")
Sheet1
|
A |
B |
C |
| 1 |
Ford |
Escort |
found |
=IF(ISNA(VLOOKUP(A1&B1,Sheet2!$A:$A,1,FALSE)),”not found”,”found”) |
| 2 |
Lotus |
Elise |
found |
=IF(ISNA(VLOOKUP(A2&B2,Sheet2!$A:$A,1,FALSE)),”not found”,”found”) |
| 3 |
BMW |
330 |
found |
=IF(ISNA(VLOOKUP(A3&B3,Sheet2!$A:$A,1,FALSE)),”not found”,”found”) |
| 4 |
Audi |
|
not found |
=IF(ISNA(VLOOKUP(A4&B4,Sheet2!$A:$A,1,FALSE)),”not found”,”found”) |
| 5 |
Mini |
Cooper |
found |
=IF(ISNA(VLOOKUP(A5&B5,Sheet2!$A:$A,1,FALSE)),”not found”,”found”) |
| 6 |
Ford |
Focus |
not found |
=IF(ISNA(VLOOKUP(A6&B6,Sheet2!$A:$A,1,FALSE)),”not found”,”found”) |
Sheet2
|
A |
B |
C |
D |
E |
| 1 |
FordEscort |
Ford |
US |
Escort |
Red |
=B1&D1 |
| 2 |
LotusElise |
Lotus |
UK |
Elise |
Silver |
=B2&D2 |
| 3 |
BMW330 |
BMW |
Germany |
330 |
Blue |
=B3&D3 |
| 4 |
VWGolf |
VW |
Germany |
Golf |
Black |
=B4&D4 |
| 5 |
FordMondeo |
Ford |
US |
Mondeo |
Silver |
=B5&D5 |
| 6 |
MiniCooper |
Mini |
|
Cooper |
|
=B6&D6 |
Also see