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.

  • Here’s what I have that returned the column label. =INDEX($B$1:$H$1,0,MATCH(MAX($B2:$H2),$B2:$F2,0)) – dalben Mar 2 '18 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 Mar 4 '18 at 5:19
0
=ARRAYFORMULA(CONCATENATE(IF(MAX(B2:D5)=B2:D5,B1:D1,)))


=ARRAYFORMULA(CONCATENATE(IF(MAX(B2:D5)=B2:D5,A2:A5,)))
  • B2:D5: Table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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