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.


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

 

[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.