Formula help....

[Guest]

Is there a way to find the following with one formula:


NAME Score
A1: Larry B1: 28
A2: Joe B2: 45
A3: Hugo B3: 25
A4: Jim B4: 42
A5: Joe B5: 33
A6: Jim B6: 22
A7: Larry B7: 31
A8: Larry B8: 42
A9: Hugo B9: 41


C1= Larry (inputted)


I need a formula (D1) that looks in column A for all of
Larry's possible point totals then can caluclate the
maximum number of points Larry got (seen in column B) - 42


TIA.

Mike

 

[Norman Harker]

Hi Mike!

Try:

=SUMPRODUCT(MAX(($A$1:$A$9=C1)*($B$1:$B$9)))

Seasons Greetings

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Alan Beban]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook:

=MAX(VLookups(C1,A1:B9,2))

Alan Beban

 

[Harlan Grove]

Try:

=SUMPRODUCT(MAX(($A$1:$A$9=C1)*($B$1:$B$9)))

...

Why the SUMPRODUCT call? It actually gives incorrect results when there are
multiple instances of the highest value. Better to use the array formula

=MAX(IF(A1:A9=C1,B1:B9))

 

[Kieran]

The array formula (use ctrl-shift-enter when you enter the formula)
should work.

=max((c1=a1:a9)*b1:b9)

 

[Norman Harker]

Thanks Harlan!

I tried:

=SUMPRODUCT(MAX(($A$1:$A$9=C1)*($B$1:$B$9)))

With multiple instances of 42 (the max) in B1:B9 it works OK. So when
do I get a problem?



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

...
..

Why the SUMPRODUCT call? It actually gives incorrect results when there are
multiple instances of the highest value. Better to use the array formula

=MAX(IF(A1:A9=C1,B1:B9))

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup

archives.

 

[Harlan Grove]

I tried:

=SUMPRODUCT(MAX(($A$1:$A$9=C1)*($B$1:$B$9)))

With multiple instances of 42 (the max) in B1:B9 it works OK. So when
do I get a problem?

....

I misread the parentheses. Your formula works, but the SUMPRODUCT call is
still unnecessary. Plain entry of

=MAX(($A$1:$A$9=C1)*($B$1:$B$9))

would be sufficient.

 

[Norman Harker]

Hi Harlan!

Relieved! Thought I was missing something.

Agreed that SUMPRODUCT call is otiose. It's only advantage is that it
does not have to be array entered.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.