reverse vlookup

[Greg]

can anyone help with a reverse type vlookup?

I have a range of data that i want to look up the max value in column
C....easy.

Now, i want to lookup (and return) the value in column A from the same row
that the max value (in column C) came from. Any help?

From here i want to build a top ten of max and subsequent maximums. ie, a
top ten gainers list (of stocks)

Any help would be appreciated greatly

Greg

 

[Anon]

can anyone help with a reverse type vlookup?

I have a range of data that i want to look up the max value in column
C....easy.

Now, i want to lookup (and return) the value in column A from the same row
that the max value (in column C) came from. Any help?

From here i want to build a top ten of max and subsequent maximums. ie, a
top ten gainers list (of stocks)

Any help would be appreciated greatly

Greg

The col A value corresponding with the largest col C value is given by
=INDEX(A:A,MATCH(MAX(C:C),C:C,0))
or
=INDEX(A:A,MATCH(LARGE(C:C,1),C:C,0))

For the second largest, use
=INDEX(A:A,MATCH(LARGE(C:C,2),C:C,0))
and so on.

 

[Niek Otten]

Hi Greg,

Let's do it in several steps; you can always integrate formulas later.
I assume your data is in A1:C15

In D1, enter 1, in D2, enter 2, etc, to D10.
Formula in E1: =LARGE($C$1:$C$15,D1)
Fill down to E10
This gives you the 10 highest values

Formula in F1: =MATCH(E1,$C$1:$C$15,0)
Fill Down to F10
This gives you the row numbers

Formula in G1: =INDEX($A$1:$A$15,F1)
Fill down to G10
This gives you the corresponding values from column A

 

[JMay]

Each of the three solutions offered are quite good, but each fails to
"consider" if you should have any duplicate values in your column C. To
control that aspect of your approach you should perhaps visit Chip Pearson's
site here at http://www.cpearson.com/excel/rank.htm .

He does a remarkable job of demonstrating how to get all your data, not just
the unique ones and only the first of any duplicates...

HTH

 

[Greg]

Thanks guys, your help has been invaluable!

Grg