Conditioned VLOOKUP

[EyalR]

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]

@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.

[#3]

@EyalR 

As variant that could be

=IF(ISNA(XMATCH($A$2:$A$11&$B$2:$B$11,'C:\Test\[FileB.xlsx]Sheet1'!$A:$A&'C:\Test\[FileB.xlsx]Sheet1'!$B:$B)),"-","Exists")

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.