Address of a Matching Cell
To find the largest value in a list you can use the MAX worksheet function. If you want to know which cell contains that value, you can use the formula below:
The MATCH function returns the position of the largest value in the list (2 in this example). Then OFFSET is used to go to that cell and the CELL worksheet function is used to get it’s address.
I usually try to avoid OFFSET because of the volatility, but in this case, the INDEX() version is shorter too, so even better !
=CELL(“address”,INDEX(A1:A3,MATCH(MAX(A1:A3),A1:A3,0)))
=CONCATENATE(“$A$”,MATCH(MAX(A1:A9),A1:A9,0))
or
=”$A$”&MATCH(MAX(A1:A9),A1:A9,0)
Yeah, I know it’s silly but it works okay for columns.
I have tried all options, except the first option.
When I exceeded cell 5000, it did not work.
Option 1 work perfectly for me.
Thank you!!