Try this: =IF(SUM(--(NOT(ISERR(FIND(A1,Sheet1!A1:P21,1))))),"Found","Not Fond") If will find the string if string Exists in anywhere in cell
Rajan. -----Original Message----- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of dguillett1 Sent: 30 April 2012 18:28 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Find the cell value from other sheet =IF(SUMPRODUCT(--(LEFT(Sheet1!$A$1:$P$21,5)=Sheet2!A1)),"Found","Not Found") Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com -----Original Message----- From: prabhat.shrivasta...@gmail.com Sent: Sunday, April 29, 2012 9:34 PM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Find the cell value from other sheet Thanks for your revert rajan, but i've one query ma sheet(report) contains text line as "A112354 xyz abc etc" or "xyz A112354 abc etc" in several row of sheet and other sheet(user id) contains only "A1123" now i want to know if "A1123" available in sheet(report) or nt... Regards, Prabhat S -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com