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 

     

     

Yury Tokarev's avatar
Yury Tokarev
Copper Contributor
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 

 

 

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

    Yury = Champion.

     

    Thanks mate, this was driving me crazy.

     

    Microsoft should make this into a function, I can see this being a common scenario and do a simple google shows im not the only one asking. 

     

    Thanks again.

Resources