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
[TABLE="class: mceItemTable, width: 195"]
<tbody>[TR]
[TD="class: xl64, align: left"]1 - Premium[/TD]
[TD="class: xl64, width: 64, align: left"] acc40402, acc40403, acc40404, acc40405, acc40406, acc40250, acc40407, acc40408, acc40252, acc40409, acc40410, acc40411, acc40412[/TD]
[/TR]
[TR]
[TD="class: xl64, align: left"]2 - Advanced[/TD]
[TD="class: xl64, align: left"] acc40414, acc40416, acc40417, acc40085, acc40418, acc40232, acc40117, acc40253, acc40083, acc40421, acc40422, acc40423, acc40424, acc40425[/TD]
[/TR]
[TR]
[TD="class: xl64, align: left"]3 - Standard[/TD]
[TD="class: xl64, align: left"] acc40399, acc40400, acc40401
[/TD]
[/TR]
</tbody>[/TABLE]

 

[markmzz]

Maybe this:

Layout

[TABLE="width: 200"]
<colgroup><col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;" span="4"> <tbody>[TR]
[TD="class: xl65, width: 67, bgcolor: transparent"]1 - Premium[/TD]
[TD="class: xl66, width: 67, bgcolor: transparent"]acc40402, acc40403, acc40404, acc40405, acc40406, acc40250, acc40407, acc40408, acc40252, acc40409, acc40410, acc40411, acc40412[/TD]
[TD="class: xl63, width: 67, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]2 - Advanced[/TD]
[TD="class: xl66, width: 67, bgcolor: transparent"]acc40402, acc40416, acc40417, acc40085, acc40418, acc40232, acc40117, acc40253, acc40083, acc40421, acc40422, acc40423, acc40424, acc40399[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]3 - Standard[/TD]
[TD="class: xl66, width: 67, bgcolor: transparent"]acc40399, acc40402, acc40401[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]*[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]acc40399[/TD]
[TD="class: xl64, bgcolor: yellow"]2 - Advanced[/TD]
[TD="class: xl64, bgcolor: yellow"]3 - Standard[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]acc40402[/TD]
[TD="class: xl64, bgcolor: yellow"]1 - Premium[/TD]
[TD="class: xl64, bgcolor: yellow"]2 - Advanced[/TD]
[TD="class: xl64, bgcolor: yellow"]3 - Standard[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]************[/TD]
[TD="class: xl65, bgcolor: transparent"]************[/TD]
[TD="class: xl65, bgcolor: transparent"]************[/TD]
[TD="class: xl65, bgcolor: transparent"]************[/TD]
[/TR]
</tbody>[/TABLE]


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