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]

you can also use,
=IF(D3=VLOOKUP(D3,$H$3:$H$14,1,0),LOOKUP(2,1/SEARCH($J$3:$J$14,A3),$J$3:$J$14),"-")

 

[Aladin Akyurek]

@ michellejames

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

What are the results you want to obtain instead of the above?



Regards,[/QUOTE]

 

[navic]

I think the formula only search mode and make not check the year.

In B2 cell, try this ARRAY formula (CSE formula) below.

=TRIM(IFERROR(INDEX(H$2:H$13,SMALL(IF(ISNUMBER(SEARCH(" "&H$2:H$13&" "," "&$A2&" ")),ROW(H$2:H$13)-MIN(ROW(H$2:H$13))+1,""),2)),"")&" "&IFERROR(INDEX(I$2:I$13,SMALL(IF(ISNUMBER(SEARCH(" "&I$2:I$13&" "," "&$A2&" ")),ROW(I$2:I$13)-MIN(ROW(I$2:I$13))+1,""),2)),"")&" "&IFERROR(INDEX(J$2:J$13,SMALL(IF(ISNUMBER(SEARCH(" "&J$2:J$13&" "," "&$A2&" ")),ROW(J$2:J$13)-MIN(ROW(J$2:J$13))+1,""),2)),"")&" "&IFERROR(INDEX(K$2:K$13,SMALL(IF(ISNUMBER(SEARCH(" "&K$2:K$13&" "," "&$A2&" ")),ROW(K$2:K$13)-MIN(ROW(K$2:K$13))+1,""),2)),""))

	A	B	C	D	E	F	G	H	I	J	K
1	Keywords	result						Type	Year	Make	Model
2	2014 bmw 535d air filter	Air Filter 2014 BMW 535d						Air Filter	2014	BMW	X5
3	bmw 535d air filter	Air Filter BMW 535d						Air Filter	2014	BMW	535d
4	13718518111							Air Filter	2014	BMW	535d xDrive
5	bmw x5 air filter	Air Filter BMW X5						Air Filter	2015	BMW	535d
6	2011 bmw x5 air filter	Air Filter BMW X5						Air Filter	2015	BMW	535d xDrive
7	2012 bmw x5 air filter	Air Filter BMW X5						Air Filter	2015	BMW	X5
8	2013 bmw x5 air filter	Air Filter BMW X5						Air Filter	2015	BMW	740Ld xDrive
9	2015 bmw x5 air filter	Air Filter 2015 BMW X5						Air Filter	2016	BMW	X5
10	cabin air filter bmw x5	Air Filter BMW X5						Air Filter	2016	BMW	535d
11	2008 bmw x5 cabin filter	BMW X5						Air Filter	2016	BMW	535d xDrive
12	2009 bmw x5 cabin filter	BMW X5						Air Filter	2017	BMW	X5
13	2011 x5 air filter	Air Filter X5						Air Filter	2018	BMW	X5
14	2013 bmw 535i air filter	Air Filter BMW
15	2013 bmw x3 air filter	Air Filter BMW
16	2013 bmw x5 cabin filter	BMW X5
17	2014 bmw x5 air filter	Air Filter 2014 BMW X5
18	2016 bmw x5 air filter	Air Filter 2016 BMW X5
19	64119248294
20	bmw cabin filter x5	BMW X5
21	bmw x5 filter	BMW X5
22	premium guard air filter	Air Filter
23	fit air filter	Air Filter
24	2014 bmw x5	2014 BMW X5
25	2015 bmw x5	2015 BMW X5
26	air 2015	2015
27	bmw filter	BMW
28	bmw air	BMW
29	air fit
30	bmw x5	BMW X5
31	bmw air filter	Air Filter BMW
32	16 air filter	Air Filter
33	premium guards
34	premium fit
35	xdrive
36	air 2014	2014
37	premium filter
38	premium air
39	16 filter
40	guard filter
41	fit filter
42	air guard
43	air filter filters	Air Filter

<tbody>
</tbody>