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?

On 8 Okt., 07:19, Sarwat wrote:
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

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
---------------
Jacob Skaria


"Sarwat" wrote:

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?