Help with Array formula for a complex lookup

[Felice]

I am trying to do a complex lookup and can't use vlookup because of the format of the data. I am trying to lookup ids (e.g. acc40402) from one list to determine their categories using a second list below. My lookup table: Col A is category and Col B is a comma separated list of accessories. I know that individually I can use the FIND() function to lookup the id in Cell B1 or B2.... what I want is to be able to look for an id in all of col B and return the corresponding value in col A for each row that id is found in. See my psuedo code below. I think it can be done with array formulas but I haven't figured out how yet.

logically what I want to do is

For each product id {
row = 0
For ( row = row + 1){
If (find (product id, B:row), A:row, 0);
}
}

The result can be an array/list of the values in col A for each of the ids used for lookup

Col A Col B

1 - Premium	acc40402, acc40403, acc40404, acc40405, acc40406, acc40250, acc40407, acc40408, acc40252, acc40409, acc40410, acc40411, acc40412
2 - Advanced	acc40414, acc40416, acc40417, acc40085, acc40418, acc40232, acc40117, acc40253, acc40083, acc40421, acc40422, acc40423, acc40424, acc40425
3 - Standard	acc40399, acc40400, acc40401

<tbody>
</tbody>

 

[markmzz]

Maybe this:

Layout

1 - Premium	acc40402, acc40403, acc40404, acc40405, acc40406, acc40250, acc40407, acc40408, acc40252, acc40409, acc40410, acc40411, acc40412
2 - Advanced	acc40402, acc40416, acc40417, acc40085, acc40418, acc40232, acc40117, acc40253, acc40083, acc40421, acc40422, acc40423, acc40424, acc40399
3 - Standard	acc40399, acc40402, acc40401
*
acc40399	2 - Advanced	3 - Standard
acc40402	1 - Premium	2 - Advanced	3 - Standard
************	************	************	************

<colgroup><col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;" span="4"> <tbody>
</tbody>

Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

In B5

=IFERROR(INDEX($A$1:$A$3,
SMALL(IF(ISNUMBER(SEARCH(","&$A5&",",","&SUBSTITUTE($B$1:$B$3," ","")&",")),
ROW($A$1:$A$3)-ROW($A$1)+1),COLUMNS($B5:B5))),"")

Markmzz