Lookup multiple results for multiple criteria

Question

I've got a list of parent SKUs for items I need to create in my inventory system. This list has been finely paired down to the 165 products we would like to carry. However, each one of these 165 SKUs has between 2 and 8 child SKUs of different colors, sizes, etc. Those are stored on a different worksheet, mixed into around 2500 items. Those are the SKUs I need to input into my inventory system.

Here is what it looks like.

Sheet 1 is just SKUs:

A
1
2
3
4

Sheet 2 is comprised of all the child SKUs, with parent SKUs in column B. Not all parents have the same number of children:

  A        B
1BLKM      1
1BLKL      1
1BLUM      1
2BLKM      2
2BLKL      2
2BLUM      2
2ORAM      2
3BLKM      3
3BLUM      3

I want to look up all of the child SKUs for the Parent SKU list that has been fine tuned. Parent SKU is included as a column on the child SKU worksheet. I need to lookup all matches of the Parent SKU, then continue to move down the parent SKU list until all matches for all 165 parent items have been found.

It seems like every function I try can't use an Array for input. Is there a way to do this with Lookup or some combination of index, match, row, etc? Any way at all to do it without VBA? Or maybe even a VBA solution with code that I can understand, as someone who hasn't used VBA before.

Answer 1

To get all the matching child SKUs in one column, you can use the following array formula in row 1 of a column and fill down as far as needed.

=IFERROR(INDEX(Sheet2!$A$1:$A$11,SMALL(IF(ISERROR(MATCH(Sheet2!$B$1:$B$11,Sheet1!$A$1:$A$5,0)),2000000,ROW(Sheet2!$B$1:$B$11)),ROW())),"")

To make this as an array formula, you must enter it by pressing Ctrl+Shift+Enter.

This is the second question I've answered with this kind of formula today, so I'll just link to my other answer for the explanation of how this formula works (see the EDIT section).

Answer 2

Here's a non-array solution. Based on your sheet2 layout where the data is all sorted by column B so like items are listed together, this little formula will do the trick. Enter the first formula in B1, then copy down and to the right as far as needed.

=IF(COUNTIF(Sheet2!$B:$B, $A1)<COLUMN(A1), "", INDEX(Sheet2!$A:$A, MATCH($A1, Sheet2!$B:$B, 0)+(COLUMN(A1)-1)))