How to return Excel row and column header values based on a known cell?

Question

I have some table data in Excel, and I've used the MAX formula to return the highest value. Now I need to write a couple formulas that return the values of the intersecting row and column headings based on that highest value.

Here's what the table inside my sample worksheet looks like.

MaxValue = 106,547            RowValue = ?????
                              ColValue = ?????

          ItemA     ItemB     ItemC     ItemD     ItemE     ItemF

  SiteA   784       3,357     -         77        8         54,789
  SiteB   589       259       89        106,547   56        2,587
  SiteC   635       678       -         9,963     -         26,993
  SiteD   1,257     9         41        589       -         6,520
  SiteE   87        3,688     476       46,719    7,899     369

The highest value contained in the table is 106,547. So I want the formulas to return ItemD for the RowValue result and SiteB for the ColValue result.

Normally I would come at this in VBA, but this particular approach needs to happen in Excel. I'm thinking INDEX MATCH is the right approach, because I can obtain the correct values using the following formulas in the worksheet (they don't quite translate to the table I posted above, but I can't post images yet, sorry).

RowValue = INDEX($B$4:$G$4,MATCH(C1,$B$6:$G$6,0))

ColValue = INDEX($A$5:$A$9,MATCH(C1,$E$5:$E$9,0))

What I need is to be able to find those values without hardcoding the row information.

Your index match formula is not working? Thats the easiest way to get it. What problem do you face? — Gowtham Shiva
– Gowtham Shiva, Commented Apr 8, 2017 at 5:30
I need a way to adjust the formulas so that they use the position of the MaxValue cell to return the headings associated with it. In this instance, the highest value is 106,547 — so the formula for the row value should return ItemD and the column value SiteB. — Ves
– Ves, Commented Apr 8, 2017 at 5:36

Subodh Tiwari sktneer · Accepted Answer · 2017-04-08 06:20:04Z

2

Assuming your data is in the range A1:G6 where A2:A6 contains the Site headers, B1:G1 contains the Item headers and numbers in the range B2:G6, then try this

To get Site header:

In J2

=INDEX(A2:A6,SUMPRODUCT((B2:G6=MAX(B2:G6))*(ROW(B2:B6)-ROW(B2)+1)))

To get Item header:

In K2

=INDEX(B1:G1,SUMPRODUCT((B2:G6=MAX(B2:G6))*(COLUMN(B1:G1)-COLUMN(B1)+1)))

Based on your file:

In F1

=INDEX(A5:A9,SUMPRODUCT((B5:G9=C1)*(ROW(B5:B9)-ROW(B5)+1)))

In F2

=INDEX(B4:G4,SUMPRODUCT((B5:G9=C1)*(COLUMN(B4:G4)-COLUMN(B4)+1)))

edited Apr 8, 2017 at 6:20

answered Apr 8, 2017 at 6:10

Subodh Tiwari sktneer

9,9862 gold badges20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to return Excel row and column header values based on a known cell?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related