reverse vlookup

  • Thread starter Thread starter Greg
  • Start date Start date
G

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
 
Greg said:
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
Click to expand...

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.
 
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
 
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
 
Back
Top