SOLVED

Vlookup more than one value to return result

%3CLINGO-SUB%20id%3D%22lingo-sub-2136509%22%20slang%3D%22en-US%22%3EVlookup%20more%20than%20one%20value%20to%20return%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2136509%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20is%20there%20a%20way%20to%20Vlookup%20more%20than%20one%20value%20to%20find%20the%20result%2C%26nbsp%3BIf%20I%20add%20the%20gender%20%26amp%3B%20age%20and%20use%20this%20to%20determine%20the%20result%20with%20Vlookup%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20use%20D%20%26amp%3B%20E%20to%20determine%20the%20result%20of%20118%20%3DVLOOKUP(E4%2C'Point%20System'!E%3AJ%2C5%2C0)%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%22Janedb_1-1613399929323.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F255015iD56EE71842B4A9B0%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Janedb_1-1613399929323.png%22%20alt%3D%22Janedb_1-1613399929323.png%22%20%2F%3E%3C%2FSPAN%3E%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%22Janedb_0-1613399892009.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F255014iAA6CD8C45F8E5231%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Janedb_0-1613399892009.png%22%20alt%3D%22Janedb_0-1613399892009.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2136509%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2136611%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20more%20than%20one%20value%20to%20return%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2136611%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F509992%22%20target%3D%22_blank%22%3E%40Janedb%3C%2FA%3E%26nbsp%3Bshort%20answer%20is%20no%2C%20but%20there%20are%20other%20ways%20to%20do%20this.%26nbsp%3B%20The%20easiest%20is%20if%20you%20have%20the%20more%20recent%20version%20of%20Excel%20and%20have%20the%20FILTER()%20function.%26nbsp%3B%20otherwise%20an%20INDEX(SUMPRODUCT())%20combination%20would%20work%20as%20follows%3A%3C%2FP%3E%3CP%3Efor%20FILTER%20you%20can%20either%20do%20a%20cascading%20filter%20where%20you%20filter%20the%20array%20based%20on%201%20criteria%20and%20then%20have%20another%20filter%20on%20that%2C%20but%20it%20is%20easier%20to%20just%20concatenate%20the%20terms%20and%20columns%20or%20interest%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DFILTER(I%3AI%2C%24E%244%26amp%3B%24D%244%3DE%3AE%26amp%3BF%3AF%2C%22n%2Fa%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eif%20you%20don't%20have%20dynamic%20arrays%20(i.e.%20you%20don't%20have%20the%20FILTER%20function)%20then%20you%20use%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(I%3AI%2CSUMPRODUCT(ROW(E%3AE)*(%24E%244%3DE%3AE)*(%24D%244%3DF%3AF)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi, is there a way to Vlookup more than one value to find the result, If I add the gender & age and use this to determine the result with Vlookup?

 

I use D & E to determine the result of 118 =VLOOKUP(E4,'Point System'!E:J,5,0)

 

Janedb_1-1613399929323.png

 

Janedb_0-1613399892009.png

 

4 Replies
best response confirmed by Janedb (Contributor)
Solution

@Janedb short answer is no, but there are other ways to do this.  The easiest is if you have the more recent version of Excel and have the FILTER() function.  otherwise an INDEX(SUMPRODUCT()) combination would work as follows:

for FILTER you can either do a cascading filter where you filter the array based on 1 criteria and then have another filter on that, but it is easier to just concatenate the terms and columns or interest:

=FILTER(I:I,$E$4&$D$4=E:E&F:F,"n/a")

if you don't have dynamic arrays (i.e. you don't have the FILTER function) then you use this:

=INDEX(I:I,SUMPRODUCT(ROW(E:E)*($E$4=E:E)*($D$4=F:F)))

 

@Janedb 

 

@mtarlerbeat me to the punch here with the most current solution. I was working on it and got interrupted. But here, if you DO have the most recent version of Excel, is a working example of the FILTER function that he mentions. You can test it by changing the input variables in the drop down spots where you can select age group or gender.

 

mathetes_0-1613405160192.png

 

@mtarler Thank you! I had to use this one and its working perfectly

=INDEX(I:I,SUMPRODUCT(ROW(E:E)*($E$4=E:E)*($D$4=F:F)))

 

I don't have FILTER, but thank you for the response