Conditioned VLOOKUP

%3CLINGO-SUB%20id%3D%22lingo-sub-2340300%22%20slang%3D%22en-US%22%3EConditioned%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2340300%22%20slang%3D%22en-US%22%3E%3CP%3E%3CEM%3EHello%20Everyone%2C%20Hope%20you're%20having%20a%20great%20day%20!%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20perform%20a%20conditioned%20VLOOKUP%20of%20sorts%2C%20I'll%20explain%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20file%20(Lets%20call%20it%20file%20A)%20with%202%20columns%2C%20NAME%20and%20ITEM.%3C%2FP%3E%3CP%3EThere's%20another%20file%20(Lets%20call%20it%20file%20B)%20with%20NAME%20and%20ITEM%20columns.%26nbsp%3B%3C%2FP%3E%3CP%3EA%20certain%20item%20can%20appear%20more%20than%20once%20(for%20different%20names).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Efor%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22EyalR_0-1620641574446.png%22%20style%3D%22width%3A%20349px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F279423i0DED4BB154DD13C5%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22EyalR_0-1620641574446.png%22%20alt%3D%22EyalR_0-1620641574446.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20check%26nbsp%3B%3CSTRONG%3Eper%20name%20in%20file%20A%3C%2FSTRONG%3E%20if%20a%20certain%20item%20exists%20in%20file%20B%20for%20that%20name.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20Bob%20should%20show%20Orange%20exists%20(since%20it%20is%20in%20file%20B).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20I%20can%20do%20this%20with%20manual%20filtering%2C%20but%20the%20question%20is%20if%20I%20can%20do%20it%20with%20some%20combination%20of%20VLOOKUP%2C%20MATCH%2C%20INDEX%2C%20conditioning%20for%20a%20large%20table%20with%20many%20rows%20of%20data%20%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20insight%20!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2340300%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2340455%22%20slang%3D%22en-US%22%3ERe%3A%20Conditioned%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2340455%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1049636%22%20target%3D%22_blank%22%3E%40EyalR%3C%2FA%3E%26nbsp%3BSince%20you%20have%20large%20tables%20with%20many%20rows%20of%20data%2C%20I%20would%20use%20Power%20Query.%20The%20attached%20file%20demonstrates%20how.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EConnect%20to%20both%20files%20(in%20this%20case%20I%20connected%20to%20two%20named%20ranges%20FileA%20and%20FileB%2C%20but%20the%20principle%20is%20the%20same)%3B%3C%2FP%3E%3CP%3EMerge%20FileA%20with%20FileB%2C%20Inner%20join%20(will%20select%20only%20matching%20records%20for%20Name%20and%20Item%3B%3C%2FP%3E%3CP%3EClean-up%20(remove%20an%20column)%3B%3C%2FP%3E%3CP%3EClose%20and%20load%20back%20to%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2340645%22%20slang%3D%22en-US%22%3ERe%3A%20Conditioned%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2340645%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1049636%22%20target%3D%22_blank%22%3E%40EyalR%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20that%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(ISNA(XMATCH(%24A%242%3A%24A%2411%26amp%3B%24B%242%3A%24B%2411%2C'C%3A%5CTest%5C%5BFileB.xlsx%5DSheet1'!%24A%3A%24A%26amp%3B'C%3A%5CTest%5C%5BFileB.xlsx%5DSheet1'!%24B%3A%24B))%2C%22-%22%2C%22Exists%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eto%20return%20all%20results%20at%20once%20if%20your%20Excel%20supports%20XMATCH%2C%20otherwise%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(%20ISNA(MATCH(A2%26amp%3BB2%2C'C%3A%5CTest%5C%5BFileB.xlsx%5DSheet1'!%24A%3A%24A%26amp%3B'C%3A%5CTest%5C%5BFileB.xlsx%5DSheet1'!%24B%3A%24B%2C0))%2C%20%22-%22%2C%20%22Exists%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20%2F%20Ctrl%2BD%20it%20down.%3C%2FP%3E%0A%3CP%3EBetter%20not%20use%20references%20on%20entire%20columns%20but%20on%20concrete%20ranges%2C%20even%20better%20is%20to%20use%20structured%20tables.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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:

 

EyalR_0-1620641574446.png

 

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.

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