Return value in column after =MAX
Posted by Rick on December 23, 2001 4:47 PM
I have a list of values in column B, D, F named No1. Column A is a list of Names. I have one cell with the formula =MAX(NO1) and it works correctly finding the highest value in NO1. Now in another cell I need to return the Name in Column A that corresponds to the cell that is located by the =MAX(NO1) formula.
The end goal is to find the name that had the highest number. I thought something like =A(ROW(MAX(NO1))) would work but no such luck. Anyone have a solution?
Thanks - Rick
Posted by Aladin Akyurek on December 24, 2001 12:56 AM
Rick --
What do you have in C and F: text, numbers, or nothing?
Aladin
========
Posted by Rick on December 24, 2001 8:38 AM
A is Name, B is January number, C is Jan Dollar amt, D is Feb number, E is Feb Dollar, F is Mar number, G is Mar dollar. BDF is named NO1. CEG is named DLR1. Rows 1-10 are headers and stats. Actual data starts A11. C3 contains the =MAX(NO1). In C4 I need to pull the name in column A that is in the same row as the result of C3
Also all data cells on this sheet are the result of a formula and require no user input. I have 3 linked workbooks with sheets on each for each month.
Rick
Posted by Aladin Akyurek on December 24, 2001 10:10 AM
Rick --
Just to make it sure:
You mean by "B is January number" 1, that's, the month number of january, right? If so, are you sure you want the Max of month numbers (in B, D, & F) instead of dollar amounts in C, E, & G?
Aladin
===========
Posted by Rick on December 24, 2001 12:28 PM
A B C D E F
PHYSICIAN JAN FEB
# $ # $
Smith 5 $600 2 $280
Doe 1 $700 8 $100
In this example, 8 would be the return of =MAX(NO1) and I need to pull the name Doe.
Posted by Aladin Akyurek on December 24, 2001 12:59 PM
Rick --
OK. You'll understand in a minute all those annoying questions on my part. Here is the formula you can use:
=IF(COUNTIF(B11:F12,MAX(NOI))=1,INDEX(A1:A12,SUMPRODUCT((B11:F12=MAX(NOI))*(ROW(B11:F12)))),"Too many Max values")
You need to adjust
B11:F12
A1:A12 (yes, from A1 on)
and you must make absolutely certain that you keep NOI in this formula as you defined it (a name that references a range of nonconsecutive cells).
Caveat. There can be more than one instance of the Max value. When that's the case, you'll get "Too many Max values".
Happy Holidays.
Aladin
=============
Posted by Aladin Akyurek on December 24, 2001 2:45 PM
Improved version...
which should be much safer:
=IF(COUNTIF(B11:F12,MAX(NOI))=1,INDEX(A1:A12,MAX(SUMPRODUCT((B11:B12=MAX(NOI))*(ROW(B11:B12))),SUMPRODUCT((D11:D12=MAX(NOI))*(ROW(D11:D12))),SUMPRODUCT((F11:F12=MAX(NOI))*(ROW(F11:F12))))),"")
=================
Posted by Rick on December 25, 2001 5:42 PM
Re: Improved version...
Aladin - Thanks the help. Works great. I hope you have had a good holiday season and it is a great new year!
Rick
