If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Find a value in table
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? |
#2
|
|||
|
|||
Find a value in table
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 |
#3
|
|||
|
|||
Find a value in table
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? |
Thread Tools | |
Display Modes | |
|
|