match value help.....

[Ajinkya]

Hi Friends,


cell B2=M2MEM555CFGL (item)

cell C2=190 (value)

cell D2= M555


i wants to look the match value of cell D2 in cell B2 and wants the value of cell C2


like, M555=190 (M555, wanted to find from M2MEM555CFGL)

 

[kaushik03]

Hi Ajinkya,


Formula at E2 would be

=IFERROR(IF(SEARCH(D1,B1)>0,C1,),"No match found")


Assuming D1=M555, B1=M2MEM555CFGL, C1=190

Regards,

Kaushik

 

[sdsurzh]

Hi,


Can you please paste the below formula in E2.


=INDEX($A$2:$D$9,MATCH(D2,$D$2:$D$9,0),3)


Thanks,

Suresh Kumar S

 

[nazmul_muneer]

Dear Ajinkya,

You can try the formula in E2

=IF(COUNTIF(B2,"*"&D2&"*")=1,C2,"Not Found")


Regards,


Muneer

 

[Ajinkya]

dear friends, thanks for your amazing formulas.


on the similar criteria , i have uploaded sample file....


https://hotfile.com/dl/178746079/40480b6/Book3.xlsx.html


where in Sheet2 model like M35 available in sheet1 item list and its qnty i wants in sheet2 as like as mentioned.


pls help

 

[Faseeh]

Hi Ajinkya,


This with refrence to your sheet, enter this:

=IFERROR(INDEX(Sheet1!$C$2:$C$1174,MATCH("*"&Sheet2!$B2&"*",Sheet1!$B$2:$B$1174,0),0),"No Match")

Press Ctrl+Shift+Enter, Hope that works.


Regards,

Faseeh

 

[Ajinkya]

Thanks Kaushik, Suresh Kumar,nazmul_muneer and finally Faseeh, its done

thanks again for your guidance