• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search the charector and provide the respective cell

S

sguna1982

Member
Dear Team,

I am trying to put formula for find the text in the particular cell and match with Name list and return the respective result from name list.

Please help.

Thanks,
Guna.S
 

Attachments

Last edited:
Hi @sguna1982

I suggest you to remove this file as it contains addresses and contact information and upload some dummy data. That is for the sake of your own interest, please don't upload personal info on public sites. Thank you.
 
Please try this in cell C2:

=INDEX(A$2:A$4,MAX(IF(ISERROR(FIND(A$2:A$4,B2)),-1,1)*(ROW(A$1:A$3)-ROW(A$1)+1)))

Press Ctrl+Shift+Enter to run, need to update cells in Red
 
Thanks for your reply, but its not working for me while i increase the data size.

I need formula to find the text in particular cell based on the reference of another column range, if the cell contains of the given range, then it should return the same value which is available in the entire column.

I hope you understand

Please help

Thanks,
Guna
 

Attachments

I guess your expected result was what written in that sheet? Yes i can explain it but first should discuss the result. Have you checked the attachment? Expected result in Col C and my formula gives the same in Col D.
 
Yes sir, I checked its working as per your attachment, but while i increasing the range for Col A as A$2:A$4361 for all the formula's, after changing the range its not return the expected result
=INDEX(A$2:A$4361,MAX(IF(ISERROR(FIND(A$2:A$4361,B2)),-1,1)*(ROW(A$1:A$4360)-ROW(A$1)+1)))
 
Hi Guna!

Try this too....

=LOOKUP(2,1/ISNUMBER(SEARCH(ListOfNames,B2)),ListOfNames)

Where listOfNames is range ($A$2:$A$4) from where you want to search for the test in a sentence.
 
Ok. I just checked it. Your Orgional file Col A contains duplicate data. secondly, Find() reports the first instance the data in col a matches the data in cell so you need to shorten the list in col A. See attached file.
 

Attachments

You must log in or register to reply here.
Back
Top