Forum Discussion
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...
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
"I feel like this should be easy but i am having to much trouble trying to do this. "
while as a human we can quickly get to pick this up there is a lot going on as it is you have 5 Cell rules with a lot of implied logic "the second heaviest left" for example.
and as you can see in some of the "code" supplied this is what Excel has to do to get the result you need.
I think you should consider other tools (excel is a spreadsheet not a database) have a try at ACCESS
while as a human we can quickly get to pick this up there is a lot going on as it is you have 5 Cell rules with a lot of implied logic "the second heaviest left" for example.
and as you can see in some of the "code" supplied this is what Excel has to do to get the result you need.
I think you should consider other tools (excel is a spreadsheet not a database) have a try at ACCESS