alternative formula for VLOOKUP since it stops with the 1st value itself

  • Thread starter Thread starter Kalaiarasan
  • Start date Start date
K

Kalaiarasan

Hi,

Am a research student. basically i have to sort some range in col. A
in descending order (as col. C) and then in col. D, i would like to
have the corresponding row value present in Col. B

I tried to use VLOOKUP. but it gives the first matching value only.

A B C D
5000 10 7000.0 70
2000 20 5000.0 10
4000 30 4000.0 30
3000 40 3900.0 90
1500 50 3000.0 40
2000 60 2000.0 20
7000 70 2000.0 20
1000 80 2000.0 20
3900 90 1500.0 50
2000 100 1000.0 80

I want in cells D7 and D8 the value of 60 and 100 respectively instead
of 20 and 20.
Please help me with suitable formula in column D

It is urgently required for me. advance thanks for the help

- kalaiarasan
 
Hi

Copy columns A and B and Paste to columns D and E
Now, Select Columns D and E only>Data>Sort>Column D>Descending
Your values in E will move with the values in column D

--

Regards
Roger Govier

Kalaiarasan said:
Hi,

Am a research student. basically i have to sort some range in col. A
in descending order (as col. C) and then in col. D, i would like to
have the corresponding row value present in Col. B

I tried to use VLOOKUP. but it gives the first matching value only.

A B C D
5000 10 7000.0 70
2000 20 5000.0 10
4000 30 4000.0 30
3000 40 3900.0 90
1500 50 3000.0 40
2000 60 2000.0 20
7000 70 2000.0 20
1000 80 2000.0 20
3900 90 1500.0 50
2000 100 1000.0 80

I want in cells D7 and D8 the value of 60 and 100 respectively instead
of 20 and 20.
Please help me with suitable formula in column D

It is urgently required for me. advance thanks for the help

- kalaiarasan

__________ Information from ESET Smart Security, version of virus
signature database 5410 (20100830) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 5410 (20100830) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
I used a spare column (actually column G) and put this formula in G1:

=A1+COUNTIF(A$1:A1,A1)/10

Copy this down as far as required (it assumes you will not have more
than 9 duplicate numbers in column A, but if you have just change the
10 to 100).

Then you can use this formula in D1:

=INDEX(B:B,MATCH(C1+COUNTIF(C$1:C1,C1)/10,G:G,0))

(again, change the 10 to 100 if necessary in your real data). Then
copy the formula down as far as you need.

Hope this helps.

Pete
 
Back
Top