# How can I find a # in a range & return the row/column titles associated with that #?

1. ## How can I find a # in a range & return the row/column titles associated with that #?

I'm trying to figure out how to do the above, and attached an Excel example. I'm trying to write a formula (likely 2 formulas) that will return the right data in cells D13, E13, D14, E14, etc. (currently hard-coded in red and highlighted in yellow in the attachment). I thought it could be some sort of INDEX/MATCH combo, or possibly OFFSET/MATCH, but can't seem to figure it out.

Basically, I have a range -- call it A1:G9. I have category titles in Row 1 and Column A. Lots of random numbers in the data range (B2:G9), all between -1 and 1 (e.g. 0.0657). I'm trying to find the 1st, 2nd, 3rd, (etc.) largest numbers in the range -- easy, with the LARGE function.

So, let's say the 1st largest number is 0.473195 (located in F9), and the 2nd largest number is 0.450069 (located in E5). How do I write a formula to tell me what's the category titles are for each of these results. For example, for the 1st largest number (happens to be in F9), I'd want a formula to return F1 ("FF") and another formula to return A9 ("99"). And for the 2nd largest number (happens to be in E5), I want a formula to return E1 ("EE") and another formula to return A5 ("55"). And so on.

I'm assuming there's a way to write a dynamic formula to do this -- simply finds the exact value that I've specified, and tells me what the category (row/column) titles are. Doubt I would need to copy the row/column titles to an outer row/column so that it would work with VLOOKUP/HLOOKUP, but I'm open to that if necessary.

Any thoughts on how to do this? Thanks for your help!  Register To Reply

2. ## Re: How can I find a # in a range & return the row/column titles associated with that

Take a look at the attached.

In my example, The table (including headers) is in A1:G10.

In I2 is
=LARGE(\$B\$2:\$G\$10,ROW(A1)) dragged down
In J2 for Column is Arrayed formula
=INDEX(\$B\$1:\$G\$1,MIN(IF(\$B\$2:\$G\$10=\$I2,COLUMN(\$B\$2:\$G\$2)-COLUMN(\$B\$2)+1)))

In K2 for Row is arrayed formula
=INDEX(\$A\$2:\$A\$10,MIN(IF(\$B\$2:\$G\$10=\$I2,ROW(\$A\$2:\$A\$10)-ROW(\$A\$2)+1)))

To create an Arrayed formula, while still in the formula bar, use CNTRL SHFT ENTER instead of a simple enter. You'll see {} around your formula if done properly.
Does this work for you?  Register To Reply

3. ## Re: How can I find a # in a range & return the row/column titles associated with that

Thanks for your reply, ChemistB. Your Excel acumen is beyond impressive. In fact, I'm struggling to understand some parts of the formula you came up with, but it seems brilliant nonetheless.

Unfortunately, the example you provided did not work when I applied it to my spreadsheet. Curiously, it was only about "half" right. When I cross-checked the Top-10 results from the output, only about 5 of them were correct. My first instinct was that a "FALSE" should be added to some of the "IF" statements. So I did so, but that didn't seem to help. I'll try to come up with another example spreadsheet to show you where your solution formula did and didn't work, and will post it back here.

In the meantime, if you or others have a solution to this elegantly simple problem, please let me know.

Thanks again.  Register To Reply

4. ## Re: How can I find a # in a range & return the row/column titles associated with that

First question, did you use CNTRL SHIFT ENTER to create those formulas such that you see the brackets around them?

If you did that and it's still not working, show me the formula's you used and I should be able to figure out what is wrong with them. I've attached a rework of your original file that you posted. Maybe that will help.  Register To Reply

5. ## Re: How can I find a # in a range & return the row/column titles associated with that

Thanks. I did do the CTRL + SHIFT + ENTER trick for the Array. Admittedly I forget why that works, but I know it does (I was more knowledgeable ab out Excel years ago).

Anyhow, here's one of my formulas. It's pasted from a Correlations Output tab, and references a Correlations tab. Here's the formula:
=INDEX(Correlations!\$A\$4:\$A\$61,MIN(IF(Correlations!\$B\$4:\$BG\$61=\$C6,ROW(Correlations!\$A\$4:\$A\$61)-ROW(Correlations!A5)+1)))

Any thoughts? Again, I found it curious that the formula works about half the time, but not all the time. If it didn't work, I'd expect it to not work at all. Hmmmm....  Register To Reply

6. ## Re: How can I find a # in a range & return the row/column titles associated with that

The final A5 should be anchored (same with your other formula for COLUMN)

=INDEX(Correlations!\$A\$4:\$A\$61,MIN(IF(Correlations!\$B\$4:\$BG\$61=\$C6,ROW(Correlations!\$A\$4:\$A\$61)-ROW(Correlations!\$A\$5)+1)))
Does that fix anything?
I was more knowledgeable ab out Excel years ago
LOL, I'm that way about a lot of things!  Register To Reply