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

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

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

Hi Nick,

To avoid getting the same lookup result when duplicates are present, you can use a combination of the INDEX, MATCH, and COUNTIF functions. Here's how you can do it:
[list=1][*] In a separate column, use the COUNTIF function to count the number of times each value appears in the original data. For example, if your data is in columns A and B, you can use the formula in cell C2 and drag it down to the end of your data.[*] In another column, use the INDEX and MATCH functions to look up the value you want based on the count of the value. For example, if you want to look up the second occurrence of the value 12, you can use the formula .

Here's how this works:

- The MATCH function looks for the value 2 in the count column (C) and returns the row number where it's found.
- The INDEX function uses that row number to return the corresponding value from the original data column (A).

By using the COUNTIF function to count the occurrences of each value, you can ensure that you get a different lookup result for each occurrence of a value.

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

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

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" 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

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

In article ,
Nick wrote:

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?

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

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" wrote:

In article ,
Nick wrote:

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?

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

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!

In article ,
Nick wrote:

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

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

FANTASTIC
It works great, thank you very much

Nick

"Domenic" wrote:

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!

In article ,
Nick wrote:

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

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

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

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

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

Hellow i have a very similar question as the one above,
i have a table that is about size 214*214 cells
It looks like this
R/C B D E F HE
3 Ajd2 Pos2 Bes2 Ger2 Red2 . . . . n
5 Ajd1 1 2 5 3 0……………………………4
6 Pos1 4 3 4 2 1…………………………200
7 Bes1 2 5 1 3 4………………………….30
Ger1 3 …………………………………………………………….1
Red1 5 …………………………………………………………………20
. ………………………………………………………………………
. ………………………………………………………………………
. ………………………………………………………………………
214 n ………………………………………………………………………


i have used aggregate function to extract top x values for a specific criteria.
For example let say I want top x values for Pos1:
I chose Pos1 from a dropdown list in a cell B218 and In cell D218 it gives me number 4 and then in cell E218 again value 4 and so on.
Now the next step is to link those values to names from D3:HE3 in the cell bellow the numbers(D219 for value in D218, E219 for value in E218... )
I manage to do that whit this formula INDEX($D$3:$HE$3;;MATCH(D218;INDEX($D$5:$HE$214;MA TCH($B$218;$B$5:$B$214;0);0);0))
Where this part
INDEX($D$5:$HE$214;MATCH($B$218;$B$5:$B$214;0);0) gives me all the numbers in the row, where Pos1 is located.
The problem that is annoying me is how to change this function that it will return for first number "4" Ajd2 and then for the second "4" Bes2 and not the Ajd2 again. Is it possible to do that without any extra rows or columns?
I can send my workbook if someone needs it to solve the problem 


I have solved it. Thnx any way.

Not to hijack this post too much, but could this formula below be modified to look up column headings instead of row values? I have a table with values from E:IQ, and would like to find the top ten values in that list (including multiple duplicates) and return the column heading associated with those values. Essentially trying to return the "names" of the columns associated with the top 10 values in a row, which are duplicated multiple times.

Thanks in advance,

Matt


[quote=Domenic;393259]
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!

SOLVED

After a bit of work, I have determined how to handle this situation with resorting to array formulas and that can handle ANY number of duplicates. The goal is to get the correct ROW number via a MATCH formula.
Usually this situation results when you want to SORT via the LARGE or SMALL formula and find the row number to look up another value via INDEX.

Assume Column A and B contain the above values. (we are not really using column A so we are sorting Column B
And assume the top row is row 2 (values 1, 12) [We must have a blank row above our data]

First, create a third column beside the first two with the following formula
=LARGE($B$2:$B$7,ROW(A1)) [create in top cell, fill down]

The values in this column should be
35
35
14
13
12
12 (as you can see, we have two sets of duplicates)

Next create a fourth column beside the third column with the following formula

=MATCH(c1,$B$2:$B$7,0) [create in top cell, fill down]

The values in this column should be
2
2 (duplicate row)
4
5
1
1 (duplicate row)

Finally create a fifth column with the following formula ((NOTE/WARNING: E1 MUST BE ZERO OR BLANK). It can not contain a header!!!

[create in top cell, fill down] =IF(D1<D2,D2,MATCH(C2,OFFSET($B$2:$B$7,E1,0,ROWS( $B$2:$B$7)-E1),0)+E1)

The values in this column should be
2
6
4
5
1
3
As you can see, no duplicate values at all. And this will work regardless of the number of duplicates.