Home |
Search |
Today's Posts |
#1
|
|||
|
|||
=Lookup()
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? |
#2
|
|||
|
|||
On Wed, 29 Jun 2005 20:39:06 +0100, ChuckM
wrote: 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? 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 |
#3
|
|||
|
|||
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 "ChuckM" wrote in message ... 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? -- ChuckM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|