+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    08-31-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    3

    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!
    Attached Files Attached Files
    Last edited by odonnela; 08-31-2011 at 12:40 PM. Reason: Added example Excel attachment

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    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?
    Attached Files Attached Files
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    08-31-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    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.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    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.
    Attached Files Attached Files
    Last edited by ChemistB; 09-01-2011 at 03:38 PM.

  5. #5
    Registered User
    Join Date
    08-31-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    3

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

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1