Feb 15 2021 06:43 AM
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)
Feb 15 2021 07:17 AM
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)))
Feb 15 2021 08:07 AM
@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.
Feb 15 2021 11:01 PM
@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)))
Feb 15 2021 11:03 PM
Feb 15 2021 07:17 AM
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)))