0

I'm trying get the row and column numbers of a cell determined by =MAX(range). screenshot of sample data

In the example above, =MAX(range) will return .02%. My first challenge is to get the row and column numbers of .02%. In this example it would be 4,7. Any ideas on how to do this would be great.

Taking it a step further, what I eventually what to return is the value of the labels which are in A2:A8 & B2:H2: for that cell. In this examples case, I would get AQU and BKU. It can be two separate formulas, one return row label and the other column value.

I've tried playing with INDEX and MATCH and managed to get the column header, but trying get both row and column labels are beyond me. Whether the final answer is a Sheets function, or a custom function in Google Apps Script doesn't matter much for my needs.

Improve this question
2
  • Here’s what I have that returned the column label. =INDEX($B$1:$H$1,0,MATCH(MAX($B2:$H2),$B2:$F2,0))
    – dalben
    Commented Mar 2, 2018 at 16:20
  • Looking back, that code is an incorrect paste. What I could do is return the column label per row., but not across the whole range. But your answer below solved everything.
    – dalben
    Commented Mar 4, 2018 at 5:19

1 Answer 1

Reset to default
0 
=ARRAYFORMULA(CONCATENATE(IF(MAX(B2:D5)=B2:D5,B1:D1,)))


=ARRAYFORMULA(CONCATENATE(IF(MAX(B2:D5)=B2:D5,A2:A5,)))
  • B2:D5: Table
Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.