Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009
    Posts
    37

    Unanswered: Horizontal Match

    Hi

    I have a bit of an odd problem here. Should be really easy but cannot work out how to do it and now it is starting to bug me.

    I have a table with column headings made up of the different employers situated in a building. The building names are the row headings. The body of the table contains the percentage share of the different employers of the building. I am now trying to add a column on the end titled "major employer". To populate this column I would need excel to look up the max value in the row and then to return the column heading for that value. I have the following formula but it does not work. I greatly suspect because it only works for "vertical" lookup as opposed the horizontal lookup I am trying to perform.

    =indirect((ADDRESS(14,MATCH(MAX(C15:L15),C15:L15,0 ))))

    cells c15:l15 contain the percentage share of the employers.
    14 is the number of the row containing the employers name.
    I basically want it come back with the name of the employer with the highest percentage contained in cells c15:L15.

    Employer1 Employer2 Employer3 Employer4 Building 1 0.26% 0.10% 1.87% 2.55%

    Anyone out there who could help?

  2. #2
    Join Date
    Oct 2009
    Posts
    8
    Because your lookup array starts in column C, you need to have:

    =indirect(ADDRESS(14,2+MATCH(MAX(C15:L15),C15:L15, 0)))

    You also had too many parentheses after indirect

  3. #3
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    INDIRECT() is volatile so you are better off using INDEX() here.

    Presumably the formula would be:
    =INDEX($C$14:$K$14,MATCH(MAX(C15:L15),C15:L15,0))


    Hope that helps...

Posting Permissions

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