Rank & then Offset

**John Bates** · 08-12-2009, 04:15 PM

Hi Excel Friends!

I am trying to create a nice equation that will look in row 35 for the highest value and then return the value that is 33 rows up and 1 column to the left. See attachment of for more details.

Basically, I need a formula to fill in cells B38:B41 (detail included in Excel doc attached).

Thanks!!
John

**shg** · 08-12-2009, 04:27 PM

In B38 and copy down,

=INDEX($C$2:$S$2, MATCH(LARGE($C$35:$S$35, ROWS(B$38:B38) ), $C$35:$S$35, 0) - 1)

**martindwilson** · 08-12-2009, 04:30 PM

=INDEX(C2:S2,,MATCH(1,C36:S36,0)-1) for rank 1
=INDEX(C2:S2,,MATCH(2,C36:S36,0)-1) for rank 2
ah i used your rank row shg has used the % row

**John Bates** · 08-12-2009, 04:35 PM

Awesome!! Thank you so much!!...works perfectly

**John Bates** · 08-12-2009, 05:48 PM

Hi shg,

Thank you for your help! One more question...how would I then pull in the value for "::unspecified::" depending on the Region:Segment that is returned?

Basically, I need to report the following in cells B38:B41
Argentina SME
Australia Consumer
Australia SMB
Argentina Consumer

I then want to pull in the following in cells C38:C41
1138
950
171
100

Does that make sense?

Thanks again!!

**shg** · 08-12-2009, 06:06 PM

C38 and down: =INDEX(INDEX($C$5:$S$34, 0, MATCH(B38, $C$2:$S$2, 0) + 1), MATCH("::unspecified::", INDEX($C$5:$S$34, 0, MATCH(B38, $C$2:$S$2, 0) ), 0) )