index,match - how to avoid same lookup when duplicates present
For the second instance, try...
=INDEX(A2:A7,SMALL(IF(B2:B7=12,ROW(A2:A7)-ROW(A2)+1),2))
....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
Hope this helps!
In article ,
Nick wrote:
I want to lookup values where duplicates exist but do not want the same
lookup result.
e.g.
1 12
2 35
3 12
4 14
5 13
6 35
How can I use Match, Index to return 1 when looking up 12, then 3 when
looking up 12, what happens now is I lookup 12 and get 1 every time. I've
used pivot tables and that works but its not ideal.
Thanks for all prev help.
Nick
|