index,match - how to avoid same lookup when duplicates present

[Nick]

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

 

[Domenic]

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!

 

[Nick]

Thanks very much for that, Ive tried to incorporate into my sheet but with no
luck as yet. In reality, the value 12 is unkown, it is found from an
INDEX/MATCH, also the row A2 would be unknown when constructing the function.
How do I incorporate your idea in such circumstances?

Thank you

 

[Domenic]

Thanks very much for that...

You're very welcome!

In reality, the value 12 is unkown, it is found from an
INDEX/MATCH...

Replace the number 12 with a reference to the cell containing the
INDEX/MATCH formula. So, for example, if D2 contains the INDEX/MATCH
formula, replace...

=12

with

=D2

also the row A2 would be unknown when constructing the function.

Can you elaborate?

 

[Nick]

My formulae is:
=INDEX(A2:A290,MATCH(LARGE(Q2:Q290,1),Q2:Q290,0))
this will lookup the largest value in column Q then return the matching
value from A (say CHESTER), Great... unfortunately if there is another value
equal in size in the Q column...
=INDEX(A2:A290,MATCH(LARGE(Q2:Q290,2),Q2:Q290,0))
will also return (CHESTER), problem is that I want to return the next one,
not the same one as the last time....

Hope you are not as confused as I must be but an answer to this one is would
be so much appreciated.

Thanks very much

Nick

 

[Domenic]

Provided that Column Q contains numbers that do not exceed 10 digits in
length, try...

=INDEX(A2:A290,MATCH(LARGE(Q2:Q290-ROW(Q2:Q290)/10^5,2),Q2:Q290-ROW(Q2:Q2
90)/10^5,0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Nick]

FANTASTIC
It works great, thank you very much

Nick

 

[Lemmesee]

How Could this be incremented if there were more than 2 instances of the
number?

 

[T. Valko]

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$11)))

** 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.