search and match the string

[michellejames]

Hi there,

Greeting! I have keywords list. I like that keywords match with our table make and model. And show "Match" word beside of match keywords.
Like column A values check in table J & K and show match word in column B

A	B	C	D	E	F	G	H	I	J	K
Keywords	Result						Type	Year	Make	Model
2018 chevy traverse air filter	Match						Air Filter	2017	Cadillac	XT5
cadillac xt5 air filter	Match						Air Filter	2017	GMC	Acadia
pa99222							Air Filter	2018	Cadillac	XT5
premium guard air filter pa99222							Air Filter	2018	GMC	Acadia
a3212c							Air Filter	2018	Chevrolet	Traverse
2017 acadia engine air filter							Air Filter	2018	Buick	Enclave
ac delco a3212c
ac delco air filter a3212c
acdelco a3212c
gm a3212c
gmc acadia air filter	Match
premium guard air filter
2017 gmc acadia	Match
chevrolet+traverse+2018	Match
fit air filter
2018 gmc acadia	Match
cadillac xt5	Match
buick enclave
chevrolet traverse	Match
gmc acadia	Match
chevrolet air filter
air fit
gmc air filter
2018 traverse	Match
chevrolet gmc	Match
guard air filter
2017 gmc
premium guards
premium fit
2017 2018
premium filter
guard filter
air guard
air filter filters
premium
2017
on guard
fit fit
2018
filters filters
air

<tbody>
</tbody>

Regards,

 

[majidsiddique]

Hi,
please try this.
=LOOKUP(2,1/SEARCH($K$2:$K$7,A2),$K$2:$K$7)

 

[aqeelnokia99]

=IFERROR(LOOKUP(2^15,SEARCH($C$3:$C$20,A3),$C$3:$C$20),"")

 

[michellejames]

Hi there,

Greeting! Thanks! This is working.

Regards

Hi,
please try this.
=LOOKUP(2,1/SEARCH($K$2:$K$7,A2),$K$2:$K$7)

 

[majidsiddique]

you are welcome!

 

[michellejames]

you are welcome!

Hi there,

Greeting! This formula is not working properly. Something wrong here. Please check my result

=LOOKUP(2,1/SEARCH($K$2:$K$14,A2),$K$2:$K$14)

A	B	C	D	E	F	G	H	I	J	K
Keywords	#N/A					Part	Type	Year	Make	Model
2014 bmw 535d air filter	535d						Air Filter	2014	BMW	X5
bmw 535d air filter	535d						Air Filter	2014	BMW	535d
13718518111	#N/A						Air Filter	2014	BMW	535d xDrive
bmw x5 air filter	X5						Air Filter	2015	BMW	535d
2011 bmw x5 air filter	X5						Air Filter	2015	BMW	535d xDrive
2012 bmw x5 air filter	X5						Air Filter	2015	BMW	X5
2013 bmw x5 air filter	X5						Air Filter	2015	BMW	740Ld xDrive
2015 bmw x5 air filter	X5						Air Filter	2016	BMW	X5
cabin air filter bmw x5	X5						Air Filter	2016	BMW	535d
2008 bmw x5 cabin filter	X5						Air Filter	2016	BMW	535d xDrive
2009 bmw x5 cabin filter	X5						Air Filter	2017	BMW	X5
2011 x5 air filter	X5						Air Filter	2018	BMW	X5
2013 bmw 535i air filter	#N/A
2013 bmw x3 air filter	#N/A
2013 bmw x5 cabin filter	X5
2014 bmw x5 air filter	X5
2016 bmw x5 air filter	X5
64119248294	#N/A
bmw cabin filter x5	X5
bmw x5 filter	X5
premium guard air filter	#N/A
fit air filter	#N/A
2014 bmw x5	X5
2015 bmw x5	X5
air 2015	#N/A
bmw filter	#N/A
bmw air	#N/A
air fit	#N/A
bmw x5	X5
bmw air filter	#N/A
16 air filter	#N/A
premium guards	#N/A
premium fit	#N/A
xdrive	#N/A
air 2014	#N/A
premium filter	#N/A
premium air	#N/A
16 filter	#N/A
guard filter	#N/A
fit filter	#N/A
air guard	#N/A
air filter filters	#N/A

<tbody>
</tbody>

conclusion: Why the following keywords show found the result? As the year is the mismatched column I. I think the formula only search mode and make not check the year. In that situation, we got the wrong result. Please suggest on this.

2011 bmw x5 air filter
2012 bmw x5 air filter
2013 bmw x5 air filter
2008 bmw x5 cabin filter
2009 bmw x5 cabin filter
2011 x5 air filter
2013 bmw x5 cabin filter



Regards,

 

[majidsiddique]

hi,
why are you not using pivot table, segregate year column and use pivot. its easy for you.

 

[michellejames]

hi,
why are you not using pivot table, segregate year column and use pivot. its easy for you.

Hi there,

Greeting! I did not use pivot table before. And I think in this situation it will not work. As the column A data need lookup.

Regards,

 

[majidsiddique]

Please Try this at C3,
=IF(H3=D3,LOOKUP(2,1/SEARCH($J$3:$J$14,A3),$J$3:$J$14),"-")
use this image before apply, segregate year column.

 

[majidsiddique]

To fix the range,
please use,
=IF(D3=$h$3:$h$14,LOOKUP(2,1/SEARCH($J$3:$J$14,A3),$J$3:$J$14),"-")