Hello,
I am attempting to find the max value in a table and return the row value and column value. For example;
1 2 3
1 0 0 0
2 0 3 0
3 0 0 0
I would like a formula that would return 2 and 2 in separate cells. I tried vlookup, but since the value of the max cell is the same as a row header it does not return the value I would like. Any help would be much appreciated!
Can there be duplicate max values?
If so, what headers should be returned?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There can be duplicate max values. The numbers returned should the lower row and column values for any duplicates.
Do you want the ACTUAL row and column numbers or do you want the row/column HEADERS?
How about posting a SMALL sample file (with duplicate max values) and show us what results you expect.
The headers.
Here is an example.
Try this...
This array formula** entered in M20:
=INDEX(M6:V6,MIN(IF(M7:V16=MAX(M7:V16),COLUMN(M7:V16)-COLUMN(M7)+1)))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
This formula entered in M19:
=INDEX(L7:L16,MATCH(MAX(M7:V16),INDEX(M7:V16,,MATCH(M20,M6:V6,0)),0))
The formula in M19 uses the result of the formula in M20.
Wow that is perfect! Thank you Tony!
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks