Home |
Search |
Today's Posts |
#1
|
|||
|
|||
lookup function?
If B1:B12 contains names/numbers not in any sort order( & some recurring) &
C1:C12 the corresponding values/names .How to get value/values from C1:C12 by verifying a value/name in D1 from B1:B12. Eg: B1 to B12 contains 1,5,2,1,4,12,3,3,5,7,5,1 and C1:C12 corresponding value/text Value in D1 yo be verified. If B1:B12 are in any sort order & without repetition ,a formula =lookup(D1,B1:B12,C1:C12) will give a value/name. |
#2
|
|||
|
|||
K.S.Warrier wrote in message ... If B1:B12 contains names/numbers not in any sort order( & some recurring) & C1:C12 the corresponding values/names .How to get value/values from C1:C12 by verifying a value/name in D1 from B1:B12. Eg: B1 to B12 contains 1,5,2,1,4,12,3,3,5,7,5,1 and C1:C12 corresponding value/text Value in D1 yo be verified. If B1:B12 are in any sort order & without repetition ,a formula =lookup(D1,B1:B12,C1:C12) will give a value/name. |
#3
|
|||
|
|||
not understood clerly. is this waht you want;?
in E1 type repeat E1 =VLOOKUP(D1,$B$1:$C$12,2,FALSE) K.S.Warrier wrote in message ... If B1:B12 contains names/numbers not in any sort order( & some recurring) & C1:C12 the corresponding values/names .How to get value/values from C1:C12 by verifying a value/name in D1 from B1:B12. Eg: B1 to B12 contains 1,5,2,1,4,12,3,3,5,7,5,1 and C1:C12 corresponding value/text Value in D1 yo be verified. If B1:B12 are in any sort order & without repetition ,a formula =lookup(D1,B1:B12,C1:C12) will give a value/name. |
#4
|
|||
|
|||
Hi, R.V
In column B(1,5,2,1,4,12,3,3,5,7,5,1),some values are repeated(eg. 1,3,5).So ,here more than one corresponding values will be in column C. How to get these values in column cells of E,by verifying values in D(viz ; 1,2,3,4,5,6,7,8,9,10,11,12).As column B contains three 1's,there will be three corresponding values in cell C1. I want a formula,if possible,for this. I hope my requirement is clear now. Thank you, KSW "R.VENKATARAMAN" wrote: not understood clerly. is this waht you want;? in E1 type repeat E1 =VLOOKUP(D1,$B$1:$C$12,2,FALSE) K.S.Warrier wrote in message ... If B1:B12 contains names/numbers not in any sort order( & some recurring) & C1:C12 the corresponding values/names .How to get value/values from C1:C12 by verifying a value/name in D1 from B1:B12. Eg: B1 to B12 contains 1,5,2,1,4,12,3,3,5,7,5,1 and C1:C12 corresponding value/text Value in D1 yo be verified. If B1:B12 are in any sort order & without repetition ,a formula =lookup(D1,B1:B12,C1:C12) will give a value/name. |
#5
|
|||
|
|||
Here's one play to try ..
Assume the data below is in B1:C12 1 Text1 5 Text2 2 Text3 1 Text4 4 Text5 12 Text6 3 Text7 3 Text8 5 Text9 7 Text10 5 Text11 1 Text12 In D1 will be entered a number from col B (1,2,3, ... in turn) to "verify" Put in the formula bar for E1: =IF(ISERROR(INDEX($C$1:$C$12,IF(LARGE((--($B$1:$B$12=$D$1)*ROW($A$1:$A$12)), ROW(A1))0,LARGE((--($B$1:$B$12=$D$1)*ROW($A$1:$A$12)),ROW(A1)),""))), "",IND EX($C$1:$C$12,IF(LARGE((--($B$1:$B$12=$D$1)*ROW($A$1:$A$12)),ROW(A1))0,LARG E((--($B$1:$B$12=$D$1)*ROW($A$1:$A$12)),ROW(A1)),""))) Array-enter the formula with CTRL+SHIFT+ENTER instead of just pressing ENTER Copy down to E12 (i.e. a range similar to the source col B) E1:E12 will return (in "reverse" order) all the values from col C corresponding to the value in col B which match that input in D1 For the sample data in B1:C12 if you input in D1: 1, you'll get: Text12 Text4 Text1 If you input in D1: 5, you'll get Text11 Text9 Text2 And so on Any unmatched inputs in D1, or if D1 is cleared will return blanks: "" in E1:E12 ----- Perhaps a slightly better way to set it up is to use D1, E1, F1, G1, etc to input all the values 1,2,3, etc and to have the corresponding values from col C for each value shown directly below in the same col If so, you could just paste into the formula bar for D2: =IF(ISERROR(INDEX($C$1:$C$12,IF(LARGE((--($B$1:$B$12=D$1)*ROW($A$1:$A$12)),R OW(A1))0,LARGE((--($B$1:$B$12=D$1)*ROW($A$1:$A$12)),ROW(A1)),"")))," ",INDEX ($C$1:$C$12,IF(LARGE((--($B$1:$B$12=D$1)*ROW($A$1:$A$12)),ROW(A1))0,LARGE (( --($B$1:$B$12=D$1)*ROW($A$1:$A$12)),ROW(A1)),""))) Array-enter the formula as before, copy D2 down to D13 (to cover the source range in col B) then just fill across as many cols as required (to F13 or G13, etc) -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- "K.S.Warrier" wrote in message ... Hi, R.V In column B(1,5,2,1,4,12,3,3,5,7,5,1),some values are repeated(eg. 1,3,5).So ,here more than one corresponding values will be in column C. How to get these values in column cells of E,by verifying values in D(viz ; 1,2,3,4,5,6,7,8,9,10,11,12).As column B contains three 1's,there will be three corresponding values in cell C1. I want a formula,if possible,for this. I hope my requirement is clear now. Thank you, KSW "R.VENKATARAMAN" wrote: not understood clerly. is this waht you want;? in E1 type repeat E1 =VLOOKUP(D1,$B$1:$C$12,2,FALSE) K.S.Warrier wrote in message ... If B1:B12 contains names/numbers not in any sort order( & some recurring) & C1:C12 the corresponding values/names .How to get value/values from C1:C12 by verifying a value/name in D1 from B1:B12. Eg: B1 to B12 contains 1,5,2,1,4,12,3,3,5,7,5,1 and C1:C12 corresponding value/text Value in D1 yo be verified. If B1:B12 are in any sort order & without repetition ,a formula =lookup(D1,B1:B12,C1:C12) will give a value/name. |
#6
|
|||
|
|||
hi,
Thanks very much for the immediate response.I have also arrived by another way. E1=if(d1=$b$1,$c$1,"")&","&if(d1=$b$2,$c$2,"")&"," &if(d1=$b$3,$c$3,"")&","&..........if(d1=$b$12,$c$ 12,"") which give the corresponding values in E1 from column C.Similarly,by copying down from E1 to E2,E3.....E12 can also be arrived Thank you, KSW "Max" wrote: Here's one play to try .. Assume the data below is in B1:C12 1 Text1 5 Text2 2 Text3 1 Text4 4 Text5 12 Text6 3 Text7 3 Text8 5 Text9 7 Text10 5 Text11 1 Text12 In D1 will be entered a number from col B (1,2,3, ... in turn) to "verify" Put in the formula bar for E1: =IF(ISERROR(INDEX($C$1:$C$12,IF(LARGE((--($B$1:$B$12=$D$1)*ROW($A$1:$A$12)), ROW(A1))0,LARGE((--($B$1:$B$12=$D$1)*ROW($A$1:$A$12)),ROW(A1)),""))), "",IND EX($C$1:$C$12,IF(LARGE((--($B$1:$B$12=$D$1)*ROW($A$1:$A$12)),ROW(A1))0,LARG E((--($B$1:$B$12=$D$1)*ROW($A$1:$A$12)),ROW(A1)),""))) Array-enter the formula with CTRL+SHIFT+ENTER instead of just pressing ENTER Copy down to E12 (i.e. a range similar to the source col B) E1:E12 will return (in "reverse" order) all the values from col C corresponding to the value in col B which match that input in D1 For the sample data in B1:C12 if you input in D1: 1, you'll get: Text12 Text4 Text1 If you input in D1: 5, you'll get Text11 Text9 Text2 And so on Any unmatched inputs in D1, or if D1 is cleared will return blanks: "" in E1:E12 ----- Perhaps a slightly better way to set it up is to use D1, E1, F1, G1, etc to input all the values 1,2,3, etc and to have the corresponding values from col C for each value shown directly below in the same col If so, you could just paste into the formula bar for D2: =IF(ISERROR(INDEX($C$1:$C$12,IF(LARGE((--($B$1:$B$12=D$1)*ROW($A$1:$A$12)),R OW(A1))0,LARGE((--($B$1:$B$12=D$1)*ROW($A$1:$A$12)),ROW(A1)),"")))," ",INDEX ($C$1:$C$12,IF(LARGE((--($B$1:$B$12=D$1)*ROW($A$1:$A$12)),ROW(A1))0,LARGE (( --($B$1:$B$12=D$1)*ROW($A$1:$A$12)),ROW(A1)),""))) Array-enter the formula as before, copy D2 down to D13 (to cover the source range in col B) then just fill across as many cols as required (to F13 or G13, etc) -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- "K.S.Warrier" wrote in message ... Hi, R.V In column B(1,5,2,1,4,12,3,3,5,7,5,1),some values are repeated(eg. 1,3,5).So ,here more than one corresponding values will be in column C. How to get these values in column cells of E,by verifying values in D(viz ; 1,2,3,4,5,6,7,8,9,10,11,12).As column B contains three 1's,there will be three corresponding values in cell C1. I want a formula,if possible,for this. I hope my requirement is clear now. Thank you, KSW "R.VENKATARAMAN" wrote: not understood clerly. is this waht you want;? in E1 type repeat E1 =VLOOKUP(D1,$B$1:$C$12,2,FALSE) K.S.Warrier wrote in message ... If B1:B12 contains names/numbers not in any sort order( & some recurring) & C1:C12 the corresponding values/names .How to get value/values from C1:C12 by verifying a value/name in D1 from B1:B12. Eg: B1 to B12 contains 1,5,2,1,4,12,3,3,5,7,5,1 and C1:C12 corresponding value/text Value in D1 yo be verified. If B1:B12 are in any sort order & without repetition ,a formula =lookup(D1,B1:B12,C1:C12) will give a value/name. |
#7
|
|||
|
|||
Glad to hear that you got it worked out ..
But if you're going that route, perhaps a way to get a cleaner output would be to replace all the commas "," in your formula with a space " " instead. (use Edit Replace: replace "," with " ") A TRIM() could then be wrapped around the revised formula to remove all the "extraneous" commas, viz.: In E1: =TRIM(IF(D1=$B$1,$C$1,"")&" "&IF(D1=$B$2,$C$2,"")&" "&IF(D1=$B$3,$C$3,"")&" "&IF(D1=$B$4,$C$4,"")&" "&IF(D1=$B$5,$C$5,"")&" "&IF(D1=$B$6,$C$6,"")&" "&IF(D1=$B$7,$C$7,"")&" "&IF(D1=$B$8,$C$8,"")&" "&IF(D1=$B$9,$C$9,"")&" "&IF(D1=$B$10,$C$10,"")&" "&IF(D1=$B$11,$C$11,"")&" "&IF(D1=$B$12,$C$12,"")) So, the output for a "1" input in D1 would look like: Text1 Text4 Text12 rather than: Text1,,,Text4,,,,,,,,Text12 Alternatively, you could use: In say, F1: =TRIM(SUBSTITUTE(E1,","," ")) where E1 holds your original formula -- Perhaps retain what was suggested earlier as "back-ups" to try, if you ever need to deal with a lot more than just 12 rows of data in cols B and C <g. Just adapt the ranges, i.e.: $C$1:$C$12, $B$1:$B$12 and $A$1:$A$12 to suit E.g. to: $C$1:$C$20000, $B$1:$B$20000 & $A$1:$A$20000 -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- "K.S.Warrier" wrote in message ... hi, Thanks very much for the immediate response.I have also arrived by another way. E1=if(d1=$b$1,$c$1,"")&","&if(d1=$b$2,$c$2,"")&"," &if(d1=$b$3,$c$3,"")&","&. ..........if(d1=$b$12,$c$12,"") which give the corresponding values in E1 from column C.Similarly,by copying down from E1 to E2,E3.....E12 can also be arrived Thank you, KSW |
#8
|
|||
|
|||
A TRIM() could then be wrapped around the
revised formula to remove all the "extraneous" commas, Typo, sorry .. 2nd line above should read: revised formula to remove all the "extraneous" spaces -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- |
#9
|
|||
|
|||
Hi Max,
Thanks for the suggetion & the new formula.Actually in my worksheet, I used the "," along with each of the text itself so that it need not again be included in the formula. Thank you K.S.Warrier "Max" wrote: Glad to hear that you got it worked out .. But if you're going that route, perhaps a way to get a cleaner output would be to replace all the commas "," in your formula with a space " " instead. (use Edit Replace: replace "," with " ") A TRIM() could then be wrapped around the revised formula to remove all the "extraneous" commas, viz.: In E1: =TRIM(IF(D1=$B$1,$C$1,"")&" "&IF(D1=$B$2,$C$2,"")&" "&IF(D1=$B$3,$C$3,"")&" "&IF(D1=$B$4,$C$4,"")&" "&IF(D1=$B$5,$C$5,"")&" "&IF(D1=$B$6,$C$6,"")&" "&IF(D1=$B$7,$C$7,"")&" "&IF(D1=$B$8,$C$8,"")&" "&IF(D1=$B$9,$C$9,"")&" "&IF(D1=$B$10,$C$10,"")&" "&IF(D1=$B$11,$C$11,"")&" "&IF(D1=$B$12,$C$12,"")) So, the output for a "1" input in D1 would look like: Text1 Text4 Text12 rather than: Text1,,,Text4,,,,,,,,Text12 Alternatively, you could use: In say, F1: =TRIM(SUBSTITUTE(E1,","," ")) where E1 holds your original formula -- Perhaps retain what was suggested earlier as "back-ups" to try, if you ever need to deal with a lot more than just 12 rows of data in cols B and C <g. Just adapt the ranges, i.e.: $C$1:$C$12, $B$1:$B$12 and $A$1:$A$12 to suit E.g. to: $C$1:$C$20000, $B$1:$B$20000 & $A$1:$A$20000 -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- "K.S.Warrier" wrote in message ... hi, Thanks very much for the immediate response.I have also arrived by another way. E1=if(d1=$b$1,$c$1,"")&","&if(d1=$b$2,$c$2,"")&"," &if(d1=$b$3,$c$3,"")&","&. ..........if(d1=$b$12,$c$12,"") which give the corresponding values in E1 from column C.Similarly,by copying down from E1 to E2,E3.....E12 can also be arrived Thank you, KSW |
#10
|
|||
|
|||
You're welcome !
Thanks for posting back .. -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP FUNCTION WITH SUMS ABILITY | Excel Discussion (Misc queries) | |||
LOOKUP FUNCTION WITH SUMS VALUES | Excel Discussion (Misc queries) | |||
Lookup Function | Excel Worksheet Functions | |||
Vector lookup function | Excel Worksheet Functions | |||
Another Lookup function, please | Excel Worksheet Functions |