I'm trying to do an Hlookup but it still doesn't return the data that I need.
Please help...
Attached is my sample file and the output that I need.
Thanks a million!
I'm trying to do an Hlookup but it still doesn't return the data that I need.
Please help...
Attached is my sample file and the output that I need.
Thanks a million!
Last edited by geng; 12-05-2010 at 11:20 PM.
Geng, I see your data and your output.. but WHY is that your output? What logic are you using to pick those values from your data?
the logic is to return the last number > 0.
Sample for PQR (A25) the last number that is >0 is 850(D7).
Well, the following formula will do what you're after, but in my triptophan-induced state, I can't help but feel there's a much simpler index/match formula I'm overlooking.. sheesh.
=INDEX(INDIRECT("B"&MATCH(A24,$A$2:$A$20,0)+1&":N"&MATCH(A24,$A$2:$A$20,0)+1),MATCH(2,1/(INDIRECT("B"&MATCH(A24,$A$2:$A$20,0)+1&":N"&MATCH(A24,$A$2:$A$20,0)+1)>0)))
After typing or editing this formula you must press CTRL+SHIFT+ENTER, not just ENTER. When done properly Excel will automatically insert braces, { }, around your formula.
Thanks Paul!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks