Lookup Matching Word Within A Sentence?

Z

zakmuh

New member
Joined
Mar 16, 2021
Messages
3
Reaction score
0
Points
0
Excel Version(s)
2013
Hi All,

I need help with a formula please. I want find the matching word within a sentence (within one cell).

For example:

From Column A (A4:A7) 'Ninja Electric Hob', I want to lookup the exact matching word (not partial match) 'Ninja' from Column C (C4:C7) and return the exact matching Title in Column E.

I've attached a screenshot from Excel.

Thanks

Zak


 

Attachments

  • 1.JPG
    1.JPG
    40.6 KB · Views: 11
[FONT=&quot]The best I could do was this one and that's based on 'partial match'[/FONT]

[FONT=&quot]=unique(if(isblank(A4:A7),"",(IFERROR(VLOOKUP("*"&C4&"*",$A$4:$A7,1,FALSE),"")))) - used the formula for each cell[/FONT]

[FONT=&quot]Issue here is, for vero keyword 'shark', it returns 'Baby play mat [/FONT][FONT=&quot]Shark[/FONT][FONT=&quot]ins' and picks the first one on the list, not the correct one below that[/FONT]

[FONT=&quot]
Ninja Electric Hob
Myninja Colouring Book
Baby play mat Sharkins
Shark Vacuum Cleaner
[/FONT]
 
Regarding cross posting zakmuh, it's only a problem if you don't supply links to wherever else you posted this topic.

It's a requirement, not just a request.

Why? Have a read of http://www.excelguru.ca/content.php?184

This topic has been successfully responded to at Mr Excel.
 
Apologies for this crosspost

Found the formula through another forum:


=ARRAYFORMULA(IFERROR(LOOKUP(2,1/SEARCH(" "&C4&" "," "&A$4:A$7&" "),A$4:A$7),""))




Cheers
 
Last edited:
You must log in or register to reply here.
Back
Top