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")


A B C < C function >
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”)


A B C D E < A function >
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