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

 

[zakmuh]

The best I could do was this one and that's based on 'partial match'

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

Issue here is, for vero keyword 'shark', it returns 'Baby play mat Sharkins' and picks the first one on the list, not the correct one below that

Ninja Electric Hob

Myninja Colouring Book

Baby play mat Sharkins

Shark Vacuum Cleaner

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1
2
3
4	Ninja Electric Hob		abc
5	Myninja Colouring Book		Ninja		Ninja Electric Hob
6	Baby play mat Sharkins		Shark		Shark Vacuum Cleaner
7	Shark Vacuum Cleaner		xyz
8
W-1

Cell Formulas
Range	Formula
E4:E7	E4	=IFERROR(TRIM(VLOOKUP("* "&C4&" *"," "&$A$4:$A$7&" ",1,FALSE)),"")

 

[jtakw]

Hi,

Is this what you need?

Book3.xlsx
	A	B	C	D	E
1
2
3
4	Ninja Electric Hob		ABC
5	Myninja Colouring Book		Ninja		Ninja Electric Hob
6	Baby play mat Sharkins		Cool
7	Shark Vacuum Cleaner		Shark		Shark Vacuum Cleaner
Sheet842

Cell Formulas
Range	Formula
E4:E7	E4	=IFERROR(LOOKUP(2,1/SEARCH(" "&C4&" "," "&A$4:A$7&" "),A$4:A$7),"")

 

[zakmuh]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1
2
3
4	Ninja Electric Hob		abc
5	Myninja Colouring Book		Ninja		Ninja Electric Hob
6	Baby play mat Sharkins		Shark		Shark Vacuum Cleaner
7	Shark Vacuum Cleaner		xyz
8
W-1

Cell Formulas
Range	Formula
E4:E7	E4	=IFERROR(TRIM(VLOOKUP("* "&C4&" *"," "&$A$4:$A$7&" ",1,FALSE)),"")

Thanks for your quick reply Fluff

Your formula only returns if the Vero keywords is exactly on the same row (A7 and C7). Please see attached screenshot.
Formula wouldn't return 'Ninja', as ninja is not on the same row (A4 and C5)

Have a solution?

 

[Fluff]

It returns it on the same row as the values in col C, which is what your code was doing.

 

[zakmuh]

Hi,

Is this what you need?

Book3.xlsx
	A	B	C	D	E
1
2
3
4	Ninja Electric Hob		ABC
5	Myninja Colouring Book		Ninja		Ninja Electric Hob
6	Baby play mat Sharkins		Cool
7	Shark Vacuum Cleaner		Shark		Shark Vacuum Cleaner
Sheet842

Cell Formulas
Range	Formula
E4:E7	E4	=IFERROR(LOOKUP(2,1/SEARCH(" "&C4&" "," "&A$4:A$7&" "),A$4:A$7),"")

Hi Jtakw

Yes yes, thats exactly how I wanted it but its not seems to be working on mine. Cells are empty? Please advice

 

[Fluff]

Is this for Google sheets?

 

[zakmuh]

Is this for Google sheets?

Yeah. I'd guess LOOKUP wouldn't work?

 

[jtakw]

Hi Jtakw

Yes yes, thats exactly how I wanted it but its not seems to be working on mine. Cells are empty? Please advice

Hmmm...it's working for me...

Book3.xlsx
	A	B	C	D	E
4	Ninja Electric Hob		ABC
5	Myninja Colouring Book		Ninja		Ninja Electric Hob
6	Baby play mat Sharkins		Shark		Shark Vacuum Cleaner
7	Shark Vacuum Cleaner		XZY
Sheet842

Cell Formulas
Range	Formula
E4:E7	E4	=IFERROR(LOOKUP(2,1/SEARCH(" "&C4&" "," "&A$4:A$7&" "),A$4:A$7),"")