=Lookup()

C

ChuckM

Not sure what is happening here. Here is the data:

ColA ColB
Service 17
Variety 1
Relations 14
Experience 20
Knowledge 10
Value 8
Reputation 3

Created 3 formulas to retreive the 1st, 2nd, and 3rd highest numbers in
the list (e.g.: Cell C1 has formula =Large(B1:B7,1), Cell C2 has
=Large(B1:B7,2), etc.)
Then I want to return the text value from column A and use:
=Lookup(C1,B1:B7,A1:A7)
This works for the first value and returns Experience
The second one returns Relations (wrong answer - should be Service)
The third one returns Relations (wright answer). . .

Any ideas?
 
R

Ron Rosenfeld

Not sure what is happening here. Here is the data:

ColA ColB
Service 17
Variety 1
Relations 14
Experience 20
Knowledge 10
Value 8
Reputation 3

Created 3 formulas to retreive the 1st, 2nd, and 3rd highest numbers in
the list (e.g.: Cell C1 has formula =Large(B1:B7,1), Cell C2 has
=Large(B1:B7,2), etc.)
Then I want to return the text value from column A and use:
=Lookup(C1,B1:B7,A1:A7)
This works for the first value and returns Experience
The second one returns Relations (wrong answer - should be Service)
The third one returns Relations (wright answer). . .

Any ideas?
Click to expand...

Column B is your lookup_vector. The values in lookup_vector must be placed in
ascending order and they are not; so you may expect to obtain wrong answers.

Try this formula instead:

=INDEX($A$1:$A$7,MATCH(C1,$B$1:$B$7,0))

Copy/drag down as needed.




--ron
 
B

Biff

Hi!

Many ways to go about this....

C1 =LARGE(B$1:B$7,ROW(1:1))

Copy down to C3

D1 =INDEX(A$1:A$7,MATCH(C1,B$1:B$7,0))

Copy down to D3

OR, eliminate the formulas in C1:C3 altogether...

D1 =INDEX(A$1:A$7,MATCH(LARGE(B$1:B$7,ROW(1:1)),B$1:B$7,0))

Copy down to D3

If you might have duplicate values in col B that changes everything!

Biff
 
Top