VLOOKUP with 2 conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-1271049%22%20slang%3D%22en-US%22%3EVLOOKUP%20with%202%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1271049%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20can%20I%20VLOOKUP%20into%20a%20seperate%20file%20using%202%20conditions%20that%20must%20be%20met%3F%26nbsp%3B%20i.e%20take%20the%20value%20that%20does%20not%20equal%20to%20zero%20for%20the%20lookup%20value%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1271049%22%20slang%3D%22en-US%22%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-1271269%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20with%202%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1271269%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F591393%22%20target%3D%22_blank%22%3E%40Leanne2490%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20sure%20that%20what%20you're%20wanting%20to%20do%20is%20clear%20in%20your%20own%20mind%3B%20your%20description%20of%20it%2C%20however%2C%20is%20not%20clear%20enough%20for%20somebody%20else%20to%20reliably%20answer.%20Could%20you%20spell%20out%20a%20little%20more%20clearly--using%20an%20example--what%20two%20conditions%20might%20be%2C%20why%20might%20one%20not%20be%20met%2C%20what%20the%20nature%20of%20the%20table%20in%20which%20we're%20doing%20the%26nbsp%3B%20%3CSTRONG%3EVLOOKUP%3C%2FSTRONG%3Eing%20is%2C%20whether%20or%20not%20you're%20expecting%20(requiring)%20an%20exact%20match%20of%20each%20of%20two%20criteria...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20see%20what%20I%20mean%3A%20there%20are%20a%20lot%20of%20questions%20that%20need%20to%20be%20answered%20before%20we%20can%20make%20an%20educated%20guess%20on%20how%20to%20make%20such%20a%20formula%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20fact%2C%20if%20you%20have%20the%20actual%20spreadsheets%20and%20can%20upload%20them%20(or%20a%20representative%20sample%20of%20them)%2C%20that%20would%20be%20ideal.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1271406%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20with%202%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1271406%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20pulling%20data%20from%20quite%20a%20large%20file%20into%20a%20seperate%20summary%20spreadsheet%20that%20deals%20with%20different%20product%20numbers%2C%20the%20first%20column%20contains%20the%20product%20numbers%20as%20shown%20in%20the%20simplified%20example..%3C%2FP%3E%3CP%3EAs%20you%20can%20see%20in%20the%20data%20pull%20file%2C%202%20product%20numbers%20appear%20twice.%26nbsp%3B%20I%20want%20to%20pull%20the%20DAV%20column%20figures%20into%20the%20Summary%20File%20based%20on%20the%20product%20number%20and%20centre.%20To%20fix%20this%2C%20I%20wanted%20to%20add%20in%20a%20second%20criteria%20to%20meet%20but%20have%20been%20unsuccessful.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1271495%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20with%202%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1271495%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F591393%22%20target%3D%22_blank%22%3E%40Leanne2490%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20approach%20is%20to%20nest%20CHOOSE%20in%20the%20table_array%20argument%20and%20concatenate%20the%20lookup%20value.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Patrick2788_0-1585748500119.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181277i3141A09FB4028841%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Patrick2788_0-1585748500119.png%22%20alt%3D%22Patrick2788_0-1585748500119.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1271499%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20with%202%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1271499%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F591393%22%20target%3D%22_blank%22%3E%40Leanne2490%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20modified%20the%20Summary%20File%20(to%20add%20in%20the%20column%20containing%20the%20second%20criterion)%20and%20then%20used%20the%20FILTER%20function%20instead%20of%20VLOOKUP.%20See%20if%20it%20delivers%20what%20you%20want.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

How can I VLOOKUP into a seperate file using 2 conditions that must be met?  i.e take the value that does not equal to zero for the lookup value

 

4 Replies
Highlighted

@Leanne2490 

I'm sure that what you're wanting to do is clear in your own mind; your description of it, however, is not clear enough for somebody else to reliably answer. Could you spell out a little more clearly--using an example--what two conditions might be, why might one not be met, what the nature of the table in which we're doing the  VLOOKUPing is, whether or not you're expecting (requiring) an exact match of each of two criteria...

 

You see what I mean: there are a lot of questions that need to be answered before we can make an educated guess on how to make such a formula work.

 

In fact, if you have the actual spreadsheets and can upload them (or a representative sample of them), that would be ideal.

Highlighted

Thanks @mathetes 

I am pulling data from quite a large file into a seperate summary spreadsheet that deals with different product numbers, the first column contains the product numbers as shown in the simplified example..

As you can see in the data pull file, 2 product numbers appear twice.  I want to pull the DAV column figures into the Summary File based on the product number and centre. To fix this, I wanted to add in a second criteria to meet but have been unsuccessful.  

 

Thanks for your help.

Highlighted

@Leanne2490 

One approach is to nest CHOOSE in the table_array argument and concatenate the lookup value.

Patrick2788_0-1585748500119.png

 

 

 

Highlighted

@Leanne2490 

 

I modified the Summary File (to add in the column containing the second criterion) and then used the FILTER function instead of VLOOKUP. See if it delivers what you want.