Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Return column containing number 1

    I have an excel file with 5 columns A1:E1 and 100 rows. Each row has numbers 1 to 5.
    I need a formula in F1 that will return the column that has the number 1 in it. So in the example below, I need it to return A for row 1, E for row 2 and D for row 3.

    A B C D E
    1 5 4 3 2
    5 4 3 2 1
    2 5 4 1 3

    Thanks for the help.

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Dear jha900

    If you can insert a row dropping your data to A2 and put the numbers 1 to 5 in Col A to E then the following formula should work.

    =HLOOKUP(MATCH(1,A3:E3,0),$A$1:$E$2,2)
    copy down to all rows to be evaluated.

    Regards,

    TD

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    This will work without the intermediate row:
    =SUBSTITUTE(ADDRESS(1,MATCH(1,A1:E1,0),4),1,"")

    Steve
    PS: The above is generic, but if you will be less than 26 columns and you will start with A, you could use
    =CHAR(64+MATCH(1,A1:E1,0))
    Last edited by sdckapr; 2011-11-29 at 15:15.

Posting Permissions

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