Index/Match maybe???

[Guest]

Excel2003 ...

Range B22 ... Names
Range B342 ... Values

Looking for Formula in Range Q3:Q42 to return:

Q3 ... "Name" associated with MAX Value found in Range B33
Q4 ... "Name" associated with MAX Value found in Range B44
Q5 ... "Name" associated with MAX Value found in Range B55

Note: also need to know how to handle if multiple "Names" found with same
MAX Value in the Range???

Thanks ... Kha

 

[T. Valko]

Try this:

Enter this formula in Q3 as an array**:

=IF(COLUMNS($A:A)<=COUNTIF($B3:$P3,MAX($B3:$P3)),INDEX($B$2:$P$2,SMALL(IF($B3:$P3=MAX($B3:$P3),COLUMN($B3:$P3)-MIN(COLUMN($B3:$P3))+1),COLUMNS($A:A))),"")

Copy across to a number of cells that will equal the maximum number of ties
in any of the rows. For example, if there are a max of 3 ties in any single
row then you need to copy the formula across to at least 3 columns. Then
copy down as needed.

** entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER)

Biff

 

[Trevor Shuttleworth]

One way:

=INDIRECT(ADDRESS(2,MATCH(MAX(B33),B33,0)+1))

If there are multiple maximum values, this will return the name of the first
one.

Regards

Trevor

 

[Guest]

Trevor ... Formula works flawlessly (Thanks) ... That said ... Can you take a
moment to give me a step by step (in order) line-item breakdown of how this
formula works? I need to ask T. Valko same thing ... His formula is flawless
too.

Thank you for the guidance ... Kha

 

[Guest]

T. ... Formula works flawlessly (Thanks) ... That said ... Can you take a
moment to give me a step by step (in order) line-item breakdown of how this
formula works? I need to ask Trevor same thing ... His formula works fine
too for what it does.

Thank you for the guidance ... Kha

 

[Trevor Shuttleworth]

OK

MAX(B33)
returns the maximum value in the range B3 to P3

MATCH(MAX(B33),B33,0)
returns the offset of the maximum value within the range

MATCH(MAX(B33),B33,0)+1
calculates the column number (taking into account we start in column B)

ADDRESS(2,MATCH(MAX(B33),B33,0)+1)
turns that into an address ... row 2, column whatever we just calculated

=INDIRECT(ADDRESS(2,MATCH(MAX(B33),B33,0)+1))
gets the value from the calculated address

So, let's say the maximum value of 9 was in cell L3 which is column 12

=MAX(B33) = 9
=MATCH(MAX(B33),B33,0) = MATCH(9,B33,0) = 11
=MATCH(MAX(B33),B33,0)+1 = MATCH(9,B33,0)+1 = 12
=ADDRESS(2,MATCH(MAX(B33),B33,0)+1) = ADDRESS(2, 12) = $L$2
=INDIRECT(ADDRESS(2,MATCH(MAX(B33),B33,0)+1)) = INDIRECT($L$2) = column
12 or whatever

Regards

Trevor

 

[Guest]

Trevor ... (Happy morning)

I placed values in the applicable Range of a Blank WorkBook & worked this
formula thru to help me better understand the Formulas & sequence of
calculations (excellent explanation) ... I have learned much from those that
are intimate with Excel & support these boards ... Please know I appreciate
the time you have taken to provide this guidance for one of my many Excel
short-comings.

My Thanks ... Have a Happy & Safe Day ... Kha

 

[Trevor Shuttleworth]

Thanks for the feedback. You have a great day too

 

[Guest]

Trevor ... (Happy morning)

It has been a long week so I am just getting back to all of this ... Since I
have had no further response from T. Valko & if you are still listening ...
Would you be able to explain formula Valko provided in same way you explained
yours (line item step by step) ... Not only is this helpful for me to better
understand the specific functions, but also the combination of functions &
order of calculations etc ... Formula is:


=IF(COLUMNS($A:A)<=COUNTIF($B3:$P3,MAX($B3:$P3)),INDEX($B$2:$P$2,SMALL(IF($B3:$P3=MAX($B3:$P3),COLUMN($B3:$P3)-MIN(COLUMN($B3:$P3))+1),COLUMNS($A:A))),"")

Thank you in advance ... Kha

 

[Trevor Shuttleworth]

Ken

sorry, that would be a "no". Good though isn't it !?

Best way is to Array Enter the formula into cell Q3 as suggested, then drag
it across and look at how the formula changes in each column.

You could also take any of the "inner" formulae and put them in a separate
cell to evaluate them.

Best I can suggest, I'm afraid. Perhaps Biff can be persuaded to clarify ?

Regards

Trevor

 

[Guest]

Trevor ... (Happy morning)

Back at it again ... And "Yes" ... Without question, the intimate knowledge
of Excel known by the Excel Magicians that support these boards is AWESOME.

Thanks for the guidance ... Kha