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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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!
 
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
 
Nick said:
Thanks very much for that...
Click to expand...

You're very welcome!
In reality, the value 12 is unkown, it is found from an
INDEX/MATCH...
Click to expand...

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.
Click to expand...

Can you elaborate?
 
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
 
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!
 
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))
Click to expand...

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.
 
Back
Top