Lookup Matching Word Within A Sentence

[zakmuh]

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

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[alansidman]

crossposted: Lookup Matching Word Within A Sentence?

 

[Fluff]

For future reference

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[zakmuh]

That doesn't sound right, there's something in 1 or both of those cells that's preventing the match.

I don't have the UNIQUE function in 2016, so I can't help with that formula.

No problem

Thank you mate

 

[zakmuh]

For future reference

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Hi
The formula you gave me working all good. Thanks again

Sorry about crosspost - i'll delete it

 

[Fluff]

No need to delete it, just let the other site know is solved.

 

[zakmuh]

Done

 

[zakmuh]

Try jtakw's formula like this

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

Hi
The above arrayformula is working flawlessly. How can I change the font colour of matched word only, like shown the screenshot below?
Cheers

 

[Fluff]

Don't know about Sheets, but you can't do that in Xl.

 

[zakmuh]

Don't know about Sheets, but you can't do that in Xl.

Great stuff. Please help me with Xl formula
Thanks