+ Reply to Thread
Results 1 to 6 of 6

Need help - Match number in column, find lowest number in that row, and return header name

  1. #1
    Registered User
    Join Date
    01-30-2014
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    12

    Unhappy Need help - Match number in column, find lowest number in that row, and return header name

    Hello, I am new here, been lurking and learning a lot. I have been able to figure a lot of things out from previous threads here but am asking for a little help with this please. Maybe there is a much easier way to do this but what I have so far is a sheet that contains a large number of zip codes in column A. I plan to have 6 or 7 zip codes across the top which each correspond to a store location. B2:H2000(ish) contain the number of miles between intersecting zip codes. What I would like to have happen is for the user to enter a zip code on a separate sheet and have the name of the nearest zip code be returned. I hoped I could figure this out on my own. I know this is probably not that difficult, I'm just not really all that bright. I hope this is a clear enough explanation of what i am trying to do here. Thanks in advance for any help.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Need help - Match number in column, find lowest number in that row, and return header

    OK if you have that data in a sheet called Zip try this formula in another sheet where Z1 is the cell with a specific zip code

    =INDEX(Zip!B1:H1,MATCH(MIN(INDEX(Zip!B2:H2000,MATCH(Z1,Zip!A2:A2000,0),0)),INDEX(Zip!B2:H2000,MATCH(Z1,Zip!A2:A2000,0),0),0))

    That will find the Z1 zip in A2:A2000 then get the minimum value form that row and return the header value from that column
    Audere est facere

  3. #3
    Registered User
    Join Date
    01-30-2014
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Need help - Match number in column, find lowest number in that row, and return header

    Thank you for such a fast response. I am anxious to try it out, unfortunately I just left my office and didnt save it where it is accessible to me now. I think I was on the right track but it only worked when the lowest number was in the left most column. Like I said...im not that bright.

    Thanks again, I think this is the last piece of the puzzle! Cant wait to try it out.

  4. #4
    Registered User
    Join Date
    01-30-2014
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Need help - Match number in column, find lowest number in that row, and return header

    Okay, now I feel really dumb. I'm sorry to have to ask again for help. Stumped again. That formula worked perfectly, thank you again. Now what I am wanting to do is return the lowest number in the row with the matching zip code. I know I should be able to figure this out, sorry and thanks for any help.

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Need help - Match number in column, find lowest number in that row, and return header

    That's just the red highlighted part from my previous formula

    =INDEX(Zip!B1:H1,MATCH(MIN(INDEX(Zip!B2:H2000,MATCH(Z1,Zip!A2:A2000,0),0)),INDEX(Zip!B2:H2000,MATCH(Z1,Zip!A2:A2000,0),0),0))

    MATCH finds the relevant row number and from that INDEX gives you the whole row, and MIN the smallest value in that range. If you put that formula in a cell, e.g. J2 to be this

    =MIN(INDEX(Zip!B2:H2000,MATCH(Z1,Zip!A2:A2000,0),0))

    then you can use the J2 value in the formula to find the header and shorten that, i.e.

    =INDEX(Zip!B1:H1,MATCH(J2,INDEX(Zip!B2:H2000,MATCH(Z1,Zip!A2:A2000,0),0),0))

  6. #6
    Registered User
    Join Date
    01-30-2014
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Need help - Match number in column, find lowest number in that row, and return header

    Thanks again, I think I'm done now. I appreciate your help very much, I'm starting to think I'm never going to get this. My brain might just not work this way. I guess it would help if I knew what i was doing.

    Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Return column header based on column criteria and number value
    By bwill22 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-07-2014, 07:33 PM
  2. [SOLVED] Find Average, Return Column Header of Number Closest To Average
    By djmyers in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 07-07-2013, 04:19 PM
  3. [SOLVED] Formula to find first negative number in row and return column header
    By spoonedmango in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-09-2012, 02:08 PM
  4. Replies: 5
    Last Post: 04-17-2012, 12:28 PM
  5. Replies: 4
    Last Post: 08-23-2005, 03:05 PM

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