How to Return Title of Row based on Ranking in Table?

[Invicta084]

Hi,

So I have a table that has title for the categories in column B9:B23 and values in C9:D23. The values in the array C9:D23 have column titles of "X" & "Y". I want to find the 3 largest number in the array as well as the three smallest, which is not an issue. I want to know what the title is in B9:B23 that corresponds to these value, the current formula I have works fine unless there is a tie in the values. My question is if there a way I can have it so titles returned correspond to the values regardless of a tie, so if row 9,10,11 are tied it gives the titles corresponding to row 9,10,11 and not just 9.

As a second question is there a way to get a similar return for the column titles "X" & "Y", so if C9 and D9 have the same value I can get the title that the large/small formula will return?

I attached a sample workbook, the red highlighted box demonstrates the functionality issue I'm having.

Demo.xlsx

Thanks for the assist.

[FlameRetired]

For the 3 top try array-entering this formula in I25 and filling down

Please Login or Register  to view this content.

this one in J25 and filling down

Please Login or Register  to view this content.

For the 3 worst array-enter this formula in I29 and filling down.

Please Login or Register  to view this content.

and this one in J29

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

[Invicta084]

That is exactly what I was trying to achieve, thank you very much! Much appreciated.

Edit: I did notice one small hiccup, when column "I" has a tied value with Column "J" and the value in column "J" appears above "I" in the list it associates the top value in either column for the titles A-O and column X instead of it's corresponding Y title as it should.

[FlameRetired]

Hmmm. That hadn't occurred to me. I'll need to spend some time trying to work that detail out.

[FlameRetired]

Try changing the array formula in J25:JJ27 to this

Please Login or Register  to view this content.

And the array formula in J29:J31 to this

Please Login or Register  to view this content.

See the attached and please let us know how this does with "live" data.

[FlameRetired]

BTW

Not to compound the problem, but there is "tie" situation not covered yet.

If there was a tie like this:

Row\Col	H	I	J
8		X	Y
9	A	0.724158159	0.495911476
10	B	0.545811272	0.525129891
11	C	0.803824078	0.766274299
12	D	0.12282898	0.975814775
13	E	0.709307208	0.975814775
14	F	0.832533562	0.975814775
15	G	0.14038092	0.14038092
16	H	0.239031558	0.907789786
17	I	0.330363801	0.502124901
18	J	0.844778913	0.235364741
19	K	0.940462218	0.461518999
20	L	0.892472335	0.488730538
21	M	0.414147825	0.399032811
22	N	0.318071703	0.273043756
23	O	0.401903223	0.425155552

how do you want this ranked? The above formulas will always return column X. The same is true if there were a 3-way tie like this:

Row\Col	H	I	J
8		X	Y
9	A	0.724158159	0.495911476
10	B	0.545811272	0.525129891
11	C	0.803824078	0.766274299
12	D	0.940462218	0.975814775
13	E	0.709307208	0.975814775
14	F	0.832533562	0.975814775
15	G	0.12282898	0.12282898
16	H	0.239031558	0.907789786
17	I	0.330363801	0.502124901
18	J	0.844778913	0.235364741
19	K	0.12282898	0.461518999
20	L	0.892472335	0.488730538
21	M	0.414147825	0.399032811
22	N	0.318071703	0.273043756
23	O	0.401903223	0.425155552

or this:

Row\Col	H	I	J
8		X	Y
9	A	0.724158159	0.495911476
10	B	0.545811272	0.525129891
11	C	0.803824078	0.766274299
12	D	0.940462218	0.975814775
13	E	0.12282898	0.975814775
14	F	0.832533562	0.975814775
15	G	0.12282898	0.12282898
16	H	0.239031558	0.907789786
17	I	0.330363801	0.502124901
18	J	0.844778913	0.235364741
19	K	0.709307208	0.461518999
20	L	0.892472335	0.488730538
21	M	0.414147825	0.399032811
22	N	0.318071703	0.273043756
23	O	0.401903223	0.425155552

or other.

[Invicta084]

Tested the formulas in the live data, works great! I never could wrap my head around multiplying two thing in the if statements, starting to become clearer to me how it works.

For the other tie situations, I think in my live data where there is a tie in the same row I am okay with it just returning the title "X".

Thanks again FlameRetired been a huge help.

[FlameRetired]

You are welcome. Thank you for the kind words and feedback.