Look Up Cells In Multiple Columns Referencing Max Value.

[Syndrome78]

I have a spreadsheet with multiple columns/rows of data. I need to find the max of one of the columns and the data in the other columns that are in same row. I've found the max, but I want to reference it in order to find the values of the other columns in the same row.

KWH KVARH KVAH PF
62.65 22.00 66.40 94.35%
53.45 20.51 57.25 93.36%
43.75 15.61 46.45 94.19%
41.05 13.46 43.20 95.02%
65.35 24.09 69.65 93.83%
39.95 14.93 42.65 93.67%
37.25 14.44 39.95 93.24%
34.00 12.43 36.20 93.92%
28.60 12.72 31.30 91.37%
26.45 13.51 29.70 89.06%
65.35(max value)

 

[SkipVought]

Hi,

Name your ranges
[tt]
=INDEX(INDIRECT(B1),MATCH(MAX(KWH),KWH,0),1)
[/tt]
where row 1 is headings and your table starts in A1.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Syndrome78]

I'm a little confused and I don't seem to have put the right formula in. The formula I came up with using what you put was:

=INDEX(INDIRECT(B1),MATCH(MAX(A2:A1018),A2:A1018,0),1)

 

[SkipVought]

INDIRECT(B1) because I have Named Ranges and each HEADING VALUE is a range name, consequently,

However, if you don't (and I strongly advocate that you do)...
[tt]
=INDEX($B2:$B1018,MATCH(MAX($A$2:$A$1018),$A$2:$A$1018,0),1)
[/tt]
Noe isn't that much less understandable than...
[tt]
=INDEX(INDIRECT(B1),MATCH(MAX(KWH),KWH,0),1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Sorry, I got twisted around...
[tt]
=INDEX(B$2:B$1018,MATCH(MAX($A$2:$A$1018),$A$2:$A$1018,0),1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Syndrome78]

Skip,

Thank you so much, it worked and I'll use named range headers in the future.

Carl

 

[GSCaupling]

You should name at least one range after Skip...

[small][navy]*********************
Their "fore" missed me, therefore their four-iron hit me, which is not what I was there for. It was an unfairway to start my day.[/navy][/small]

 

[SkipVought]

GS, I LOVE it...

Their "fore" missed me, therefore their four-iron hit me, which is not what I was there for. It was an unfairway to start my day.

How 'bout this...
Althought they're for fore, their "fore" missed me, therefore their four-iron hit me, which is not what I was there for. It was an unfairway to start my day and it teed me off.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GSCaupling]

Skip,

Thanks. I thought about including "they're for... something" but the thing was getting long. It would've been something like "they're forlorn..." Heck, I had to go to small print just to make it a one-liner, so to speak.

GS

[small][navy]*********************
Their "fore" missed me, therefore their four-iron hit me, which is not what I was there for. It was an unfairway to start my day.[/navy][/small]

 

[SkipVought]

To be safe on the FOURTH, don't take a FIFTH on the THIRD or you might not come FORTH on the FIFTH FORTHWITH.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}