Conditioned VLOOKUP

Occasional Visitor

Hello Everyone, Hope you're having a great day ! 


I want to perform a conditioned VLOOKUP of sorts, I'll explain:


I have a file (Lets call it file A) with 2 columns, NAME and ITEM.

There's another file (Lets call it file B) with NAME and ITEM columns. 

A certain item can appear more than once (for different names). 


for example:




I want to check per name in file A if a certain item exists in file B for that name. 


For example, Bob should show Orange exists (since it is in file B). 


I know I can do this with manual filtering, but the question is if I can do it with some combination of VLOOKUP, MATCH, INDEX, conditioning for a large table with many rows of data ? 


Thank you for your insight ! 

2 Replies

@EyalR Since you have large tables with many rows of data, I would use Power Query. The attached file demonstrates how.


Connect to both files (in this case I connected to two named ranges FileA and FileB, but the principle is the same);

Merge FileA with FileB, Inner join (will select only matching records for Name and Item;

Clean-up (remove one column);

Close and load back to Excel.


As variant that could be


to return all results at once if your Excel supports XMATCH, otherwise

=IF( ISNA(MATCH(A2&B2,'C:\Test\[FileB.xlsx]Sheet1'!$A:$A&'C:\Test\[FileB.xlsx]Sheet1'!$B:$B,0)), "-", "Exists")

and drag / Ctrl+D it down.

Better not use references on entire columns but on concrete ranges, even better is to use structured tables.