Try this array formula** :

=INDEX(B$1:C$5,MATCH(MAX(B$1:B$5+C$1:C$5),B$1:B$5+ C$1:C$5,0),ROWS(F$2:F2))

Copy down to F3

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
Here's my table:

A B C D
Nuts 21 8 Pins
Bolts 11 13 Screws
Screws 12 11 Nuts
Pins 6 14 Bolts
Caps 4 19 Nuts

I'm using =MAX(B1:B5)+(C1:C5) to get the max value returned on cell F1.
What
i need is to be able to display the following:
cell F2 to show the value from column B of the MAX combination
cell F3 to show the value from column C of the MAX combination

From example above, MAX should return 29 from row 1. How do i then display
B1 and C1 (in different cells) that adds up to the MAX value? Thanks in
advance for any assistance!