Index Match Large Duplicate Values

[auxaurores]

Hi,

I cannot return each corresponding fruit to each largest price in below table.
I have been trialing with Index, Match, Vlookup, and Row and keep failing even with a helper column Countif because I have duplicate values
Checked out these old threads as well and have not been able to replicate the correct results.

What am I missing?

Original data:

Apple	10
Orange	11
Strawberry	9
Pear	5
Banana	10

Analysed data (using Large() fyi, cannot use Max for other reasons):

?	11
?	10
?	10
?	9
?	5

Thanks for any help,

http://www.mrexcel.com/forum/excel-q...g-results.html

http://www.mrexcel.com/forum/excel-q...te-values.html

[JohnTopley]

Assuming data in A1:B5

in C1

=B1+ROWS($1:1)*10^-4

in D1

=INDEX($A$1:$A$5,MATCH(LARGE($C$1:$C$5,ROWS($1:1)),$C$1:$C$5,0))

copy down

[auxaurores]

This completely does it...
What if I wanted to add a criteria in my large i.e. match the large only where name = Peter?

For example:

John	Apple	10
Will	Orange	11
Mary	Strawberry	9
Peter	Pear	5
John	Banana	10

[Tony Valko]

Like this...

Data Range

	A	B
1	Apple	10
2	Orange	11
3	Strawberry	9
4	Pear	5
5	Banana	10
6		------
7	Orange	11
8	Apple	10
9	Banana	10
10	Strawberry	9
11	Pear	5

This array formula** entered in A7 and copied down:

=INDEX(A:A,SMALL(IF(B$1:B$5=B7,ROW(B$1:B$5)),COUNTIF(B$7:B7,B7)))

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

[auxaurores]

Like this...

Data Range

	A	B
1	Apple	10
2	Orange	11
3	Strawberry	9
4	Pear	5
5	Banana	10
6		------
7	Orange	11
8	Apple	10
9	Banana	10
10	Strawberry	9
11	Pear	5

This array formula** entered in A7 and copied down:

=INDEX(A:A,SMALL(IF(B$1:B$5=B7,ROW(B$1:B$5)),COUNTIF(B$7:B7,B7)))

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

Thanks again, this solution is working as well!

What if I wanted to add a criteria in my large i.e. match the large only where name = John?

For example:

John	Apple	10
Will	Orange	11
Mary	Strawberry	9
Peter	Pear	5
John	Banana	10

[Tony Valko]

Which bit of information are you wanting to extract?

[auxaurores]

I want to extract the fruit description based on: largest price where name = John.

I guess the formula would look like this in G2 where:

• Column A: Names
• Column B = Fruits
• Column C = Prices
• Column D:=C1+ROWS($1:1)*10^-4

=INDEX($B$2:$B$6,MATCH(LARGE(IF(A:A = "Peter"),$C$2:$C$6,ROWS($1:1)),$C$2:$C$6,0))

Obviously this does not work...

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E	F
1	John	Apple	10		John	Apple
2	Will	Orange	11			Banana
3	Mary	Strawberry	9
4	Peter	Pear	5
5	John	Banana	10
6	------	------	------	------	------	------

This array formula** entered in F1:

=IFERROR(INDEX(B:B,SMALL(IF(A$1:A$5=E$1,IF(C$1:C$5=MAX(IF(A$1:A$5=E$1,C$1:C$5)),ROW(C$1:C$5))),ROWS(F$1:F1))),"")

** 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 until you get blanks.

[auxaurores]

Try this...

Data Range

	A	B	C	D	E	F
1	John	Apple	10		John	Apple
2	Will	Orange	11			Banana
3	Mary	Strawberry	9
4	Peter	Pear	5
5	John	Banana	10
6	------	------	------	------	------	------

This array formula** entered in F1:

=IFERROR(INDEX(B:B,SMALL(IF(A$1:A$5=E$1,IF(C$1:C$5=MAX(IF(A$1:A$5=E$1,C$1:C$5)),ROW(C$1:C$5))),ROWS(F$1:F1))),"")

** 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 until you get blanks.

Capture.PNG
Using your formula, I changed one of the names to John. However the formula is not pulling it the third expected value, only the duplicated ones.
Is it normal?

[Tony Valko]

I can't see PNG images on this site.



The formula will only return the corresponding max value(s) for the single name listed.

In the posted example John had duplicate max values.

[JohnTopley]

Data in A1:C5

Please Login or Register  to view this content.

in D1 and copy down

=C1+ROWS($1:1)*10^-4

in E1 ... name e.g John

in F1

=IFERROR(INDEX($B$1:$B$5,MATCH(LARGE(($D$1:$D$5)*($A$1:$A$5=$E$1),ROWS($1:1)),$D$1:$D$5,0)),"")

Enter with Ctrl+Shift+Enter