Search Comments for Multiple Text Strings

[jham808]

Hello,

I'm trying to create a function that allows me to search a range of comments based upon multiple search keywords. For example, the user would type in a single cell "dog, cat, par". The function would then search a range of comments and return, "Burt pet shop is great because of the cute dogs and cats, but their parrot selection is limited". I cannot use a VBA for this. The text will be partial strings and the user will only input by typing words and separating via comma. Also the lookup will need to related to a unique id or title. Any thoughts on how to accomplish this? I have attached a sample file to better represent what I'm trying to accomplish.

[jayajaya_4]

=IFERROR(INDEX(Sheet2!$C$3:$C$7,MATCH(1,INDEX(ISNUMBER(SEARCH(D3,Sheet2!$C$3:$C$7))*(Sheet2!$B$3:$B$7=B3),,),0)),"No comments")

try this

[jham808]

Thanks for the quick response! this seems like a close solution; however, it doesn't allow for multiple keywords searches. For example if the cell has both dog and fish the formula would return "No comment". Any thoughts on how to allow for multiple text string searches?

[samba_ravi]

see the attached formula - that was an array formula

[jayajaya_4]

macro can work in that case..but I don't know vba sorry

[Ali Kırksekiz]

Please Login or Register  to view this content.