Forum Discussion

David Attard's avatar
David Attard
Copper Contributor
Sep 04, 2017
Solved

Using Excel lookup based on multiple criteria

Hi All, Please help me as i am frustrated to the point of giving up.   I am trying to use excel to lookup a value, based on that value look with another cell and find the highest number associated...
Formulas & Functions
Need Help
  • Yury Tokarev's avatar
    Yury Tokarev
    Sep 04, 2017

    Hi David,

     

    please see attached for the sample solution. For the second highest rep I used the following formula (array entered):

    {=INDEX($E$2:$E$500,LARGE(IF($C$2:$C$500=K1,ROW($C$2:$C$500)-ROW(INDEX($C$2:$C$500,1,1))+1),2))}

    Similarily, for the second highest weight I used

    {=INDEX($F$2:$F$500,LARGE(IF($C$2:$C$500=K1,ROW($C$2:$C$500)-ROW(INDEX($C$2:$C$500,1,1))+1),2))}

     

    Hope this helps

    Regards

    Yury 

     

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 04, 2017

Another variant is without array formulas

First largest

=AGGREGATE(14,6,(1/($C$2:$C$500=$K$1))*(E2:E500),1)

Second largest

=AGGREGATE(14,6,(1/($C$2:$C$500=$K$1))*(E2:E500),2)

etc.

  • David Attard's avatar
    David Attard
    Copper Contributor
    Sep 05, 2017

    Hi Sergei,

    Just letting you know your solution works however it was not quite what i was trying to achive. The result would sometimes be the wrong value and so i went with the array solution.

     

    I appreciate you taking the time to assist me though.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Sep 05, 2017

      Hi David,

       

      No problem, in my formula weight filter was missed. In general you may add as many filters as needed adding the multiplier. Formulas are

      =AGGREGATE(14,6,(1/($C$2:$C$500=$K$1))*(E2:E500)*($F2:$F$500=$L$9),1)
=AGGREGATE(14,6,(1/($C$2:$C$500=$K$1))*(E2:E500)*($F2:$F$500=$L$10),1)
=L4-L5
=L9-L10
=L7/L6
=AGGREGATE(14,6,(1/($C$2:$C$500=$K$1))*(F2:F500),1)
=AGGREGATE(14,6,(1/($C$2:$C$500=$K$1))*(F2:F500),2)

      and in attached file

       

       

Resources