How Could this be incremented if there were more
than 2 instances of the number?
For the second instance, try...
=INDEX(A2:A7,SMALL(IF(B2:B7=12,ROW(A2:A7)-ROW(A2)+1),2))

Try it like this...

Let's assume you want the results starting in cell D1.

Array entered**

=INDEX(A:A,SMALL(IF(B2:B7=12,ROW(A2:A7)),ROWS(D$1: D1)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down as needed.

--
Biff
Microsoft Excel MVP


"Lemmesee" wrote in message
...
How Could this be incremented if there were more than 2 instances of the
number?

"Domenic" wrote:

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