Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
2  
whats the expected output? How it should look? –  Siva Charan Apr 5 '12 at 16:53
    
All I need are results pulled from Column A of worksheet 2. My example doesn't have any, but there are a number of SKUs on Worksheet 2 that I'm just trying to ignore by only pulling the ones I want with Sheet1's list of parent SKUs. So the output should look just like sheet 2's column A. –  Matt Apr 5 '12 at 17:01
    
Have you tried a pivottable, filtering by what is in column B in sheet 2? –  soandos Apr 5 '12 at 17:13
    
That's not a bad idea, but it's 165 values that I'm filtering for (from Sheet 1). Can I filter Sheet 2's column B by Sheet 1's column A in a pivot table? –  Matt Apr 5 '12 at 17:31

2 Answers 2

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

share|improve this answer

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)))

enter image description here

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.