Find a value in table

[Sarwat]

There two columns lets say A & B. Both contains text for e.g A : A1=apple,
A2=ball, A3=cat..... and B: B1= cat,dolphin,bear , B2=apple,orange,banana ,
B3=ball,box,bat.....

now i want to search the value of a cell in A Column lies where in B
column...i.e. A1 lies in B2, A2 in B3 and so on....

which formula can do the job?

 

[Bernd P]

There two columns lets say A & B. Both contains text for e.g A : A1=apple,
A2=ball, A3=cat..... and B: B1= cat,dolphin,bear , B2=apple,orange,banana ,
B3=ball,box,bat.....

now i want to search the value of a cell in A Column lies where in B
column...i.e. A1 lies in B2, A2 in B3 and so on....

which formula can do the job?

Enter into C1:
=MATCH(A1,$B$1:$B$100,0)
and copy down as far as necessary. You will get the corresponding row
numbers.

Regards,
Bernd

 

[Jacob Skaria]

For data in this format

ColA Col B Col C
apple cat,dolphin,bear =formula
ball apple,orange,banana =formula
cat ball,box,bat =formula

In cell C1
=ADDRESS(MATCH("*" & A1 &"*",B:B,0),COLUMN(B1))

OR
=ADDRESS(ROW(A2),1) & " found in " & ADDRESS(MATCH("*" & A2
&"*",B:B,0),COLUMN(B2))

If this post helps click Yes