Looking for index match type formula using two wildcard references into a column

[silverback24]

Basically I have 3 columns of data. A and B have single part numbers, C has kits with these part numbers and other data. I am looking for a formula that will let me search with wildcards in all of column C for a kit that has both parts A and B and return the kit number to column D. C's kit numbers are not all uniform in nature hence the need for wildcards.

I am matching up our part numbers to corresponding competitor kits, so I have our kits broken into parts and am searching for their kits that contain both of the parts.

Thanks in advance for your help.

 

[silverback24]

I was hoping something like this would work: {=index(kit column,match(and("*"&partA&"*","*"&partB&"*"),kit column,0))}

 

[Marcelo Branco]

Hi,

Could you post a data sample and expected result?

M.

 

[silverback24]

Hope this helps. Not sure how to post a workbook onto here.
If A is blank then blank, means they don't have a matching part to ours. Example searching for A4 and B4 should return LW-RSMK44216-44216-681-2-4 or RSMK44216-44216-681-2-4. Same kit, lw- means comes with warranty.

Part A	Part B	Matching Kit number		Kit Numbers
	394			RSCD6375-6375-591-2-4
	394			RSCD6582-6582-673-2-4
RSMK44216	681			RSMK41434-41434-1156-2-4
RSMK44216	681			RSMK65042-65042-727-2-4
	682			RSMK64014-64014-648-2-4
	493			LW-RSMK65042-65042-727-2-4
	946			RSMK44142-44142-819-2-4
RSMK44006	558			CBO6364650
RSMK44006	558			RSCD41058-41058-562-2-4
RSMK44285	918			RSCD41277-41277-914-2-4
RSMK44175	558			LW-RSMK6384-6384-820-2-4
RSMK44175	558			RSCD65095-65095-1028-2-4
RSMK44175	558			RSMK6476-6476-473-2-4
RSMK44276	1098			RSMK65049726
RSMK44281	1107			RSCD41352-41352-815-2-4
RSMK44057	736			RSCD41123-41123-653-2-4
RSMK44057	736			RSMK41268-41268-707-2-4
RSMK44231	839			RSMK64101-64101-934-2-4
RSMK44206	839			RSMK41323-41323-440-2-4
RSMK44231	839			LW-RSMK64014-64014-648-2-4
RSMK44206	839			RSMK41272-41272-817-2-4
RSMK44195	687A			RSMK4289-4289-465-2-4
RSMK44252	918			RSMK41272-41272-923-2-4
	682			RSMK4466-4466-696-2-4

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>

 

[Marcelo Branco]

Try this array formula in C2 copied down

=IF(A2="","",IFERROR(INDEX($D$2:$D$1000,MATCH(1,IF(ISNUMBER(SEARCH(A2,$D$2:$D$1000)),IF(ISNUMBER(SEARCH("-"&B2&"-",$D$2:$D$1000)),1)),0)),"Not Found"))

confirmed with Ctrl+Shift+Enter simultaneously, not just Enter
(hold down both Ctrl and Shift keys and hit Enter)

Hope this helps

M.

 

[silverback24]

This works! I had to replace the "-" with "*" because not all the kits have -partb- format, but that is easy to do. Thanks!

 

[Marcelo Branco]

This works! I had to replace the "-" with "*" because not all the kits have -partb- format, but that is easy to do. Thanks!

You are welcome. Glad for helping.

M.