vlookup for the second value of a list

[alext49]

Hi,

I essentially want to perform a vlookup of the second (not top) lookup value
in an array. So if I have the following data set:

1 A 100
2 B 78
3 A 63
4 C 52
5 C 49
6 B 24


....I want to turn it into this on another page:

A 100
A 63
B 78
B 24
C 52
C 49


Getting the top A to return 100 is easy--it's just a pure vlookup. But
getting it to retun the second-ranked A is harder since vlookup just looks to
the #1 A and brings 100 again. Does anyone have any thoughts or suggestions
for a formula (or formulas)?

 

[PCLIVE]

Why wouldn't you just sort the data? That seems to be what you are doing.

 

[T. Valko]

Are there *always* no more than 2 of each value?

Biff

 

[alext49]

Maybe I didn't quite explain it enough...sorting doesn't work for what I'm
trying to do. I'm using Data Validation to pull only the A's (layed out on
the page in a certain way), only the B's. And it's not simply one column
which needs to be sorted. Instead, it's A-->Z (about 30 data sets) and 22
different groupings which need to be laid out on a page based on whichever
data set is being validated (A-->Z). It's complicated.

Anyways, is there a lookup for the second largest value?

 

[alext49]

Correct...there are never more than 2.

Thanks in advance...

 

[T. Valko]

Ok...

With this data in the range A1:B6

A 100
B 78
A 63
C 52
C 49
B 24

This data in the range F1:F6

A
A
B
B
C
C

Enter this formula in G1:

=VLOOKUP(F1,A1:B6,2,0)

Enter this formula in G2 and copy down to G6:

=IF(F2=F1,LOOKUP(2,1/(A$1:A$6=F2),B$1:B$6),VLOOKUP(F2,A$1:B$6,2,0))

Biff

 

[driller]

alext49,

did u mean the list may contain something like this,,,,no more than 2
"value" on first and second column.

D 100
D 100
F 100
F 100
E 100
E 100

or u mean like this...no more than 1 "value" on second column

D 100
D 50
F 100
F 50
E 100
E 50

regards,
driller

 

[Mike H]

Or to do it in a single formula use:-

=INDEX(A1:B7,SMALL(IF(A1:B7="a",ROW(A1:B7)-ROW(A1)+1,ROW(B7)+1),2),2)

The second to last 2 can be changed to make it find the Nth occurence.

It's an array so Ctrl+Shift+enter.

Mike

 

[alext49]

This works...you rock. Thanks for the help!!!

Ok...

With this data in the range A1:B6

A 100
B 78
A 63
C 52
C 49
B 24

This data in the range F1:F6

A
A
B
B
C
C

Enter this formula in G1:

=VLOOKUP(F1,A1:B6,2,0)

Enter this formula in G2 and copy down to G6:

=IF(F2=F1,LOOKUP(2,1/(A$1:A$6=F2),B$1:B$6),VLOOKUP(F2,A$1:B$6,2,0))

Biff

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

This works...you rock. Thanks for the help!!!