Looking up 3 cells based on defined value in a row/column location

G

Guest

I have this data. The 17894 in the last row( C17) is the max of C3:F16. That
value is located in D16. Is there a way that I can have a cell produce these
results -
the # in row 2 ( 101,102, 103 or 104), and the day and date in the A & B
column to indicate the max value in this case (17894) is from --->>> #102
on Wed 6/13 ?

A B C D
E F
101 102 103 104
Thu 5/31/2007 17485 12375 14821 9613
Fri 6/1/2007 17533 13059 14090 11798
Sat 6/2/2007 11549 14634
Sun 6/3/2007 17106 16079 13112
Mon 6/4/2007 16529 15759 16333 11113
Tue 6/5/2007 15756 15968 12519 14690
Wed 6/6/2007 17214 13197 14304 7644
Thu 6/7/2007 16957 14613 15106 12105
Fri 6/8/2007 14951 10369 14718 5468
Sat 6/9/2007 15027 14302 13777
Sun 6/10/2007 17861 14707 15678 9403
Mon 6/11/2007 17155 14791 10892 10225
Tue 6/12/2007 15942 15021 15156 14240
Wed 6/13/2007 17489 17894 13917 15599
17894

Thanks,

Steve
 
T

T. Valko

Try these:

B3:B16 = date
C3:F16 = rng
C17 = a formula (as per your description): =MAX(rng)

Assume you want the date in cell B20. For the date use this array formula**:

=INDEX(Date,MATCH(TRUE,MMULT(--(rng=C17),TRANSPOSE(COLUMN(rng)^0))>0,0))

For the column header (dependent upon the date formula cell B20):

=INDEX(C2:F2,MATCH(C17,INDEX(rng,MATCH(B20,Date,0),),0))

For the weekday (dependent upon the date formula cell B20):

=TEXT(B20,"ddd")

Biff
 
T

T. Valko

I forgot something:
For the date use this array formula**:
Click to expand...

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

Biff
 
G

Guest

Perfect solution.
Thanks so much.

Steve

T. Valko said:
I forgot something:


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

Biff
Click to expand...
 
Top