SOLVED

Contributor

Vlookup more than one value to return result

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)

4 Replies
best response confirmed by Janedb (Contributor)
Solution

Re: Vlookup more than one value to return result

@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)))``

Re: Vlookup more than one value to return result

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

Re: Vlookup more than one value to return result

@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)))`

Re: Vlookup more than one value to return result

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