lookup function?

[Guest]

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.

 

[R.VENKATARAMAN]

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.

 

[R.VENKATARAMAN]

not understood clerly. is this waht you want;?

in E1 type repeat E1

=VLOOKUP(D1,$B$1:$C$12,2,FALSE)

 

[Guest]

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

 

[Max]

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)

 

[Guest]

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]

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

 

[Max]

A TRIM() could then be wrapped around the

revised formula to remove all the "extraneous" commas,

Typo, sorry .. 2nd line above should read:

 

[Guest]

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]

You're welcome !
Thanks for posting back ..