Look up value in a list and return multiple corresponding values?

[jjacks60]

Hi all,

Currently I have a formula which returns the data I need EXCEPT when there is a repeated SKU number that I'm referencing. Then it simply returns the first SKU number reference every time.

Current Formula: =OFFSET(INDEX('Production Data'!M:M,MATCH('Raw Data'!$A4,'Production Data'!$A:$A,0)),2,0)

As you can see the SKU's are in column A, but the volumes are offset two rows from it in column M.

Is there a way to adjust my formula into one that can return multiple SKU's considering this formula is an array (I've barely worked with array formulas).
The formula below isn't an index match which is throwing me off.


<code>=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))</code>

For instance, the index references the whole array. My array would be column A and column M.
I've tried to adjust the formula below to match my parameters. Wasn't sure where to plug in the offset and match functions though or if I even needed them still.

<code>=IF(ISERROR(INDEX($A:$A & $M:$M,SMALL(IF($A:$A=$A4,ROW($A:$A)),ROW(1:1)),13 or COLUMN M)),"",INDEX($A:$A & $M:$M</code>,SMALL(IF($A:$A=$A4,ROW($A:$A)),ROW(1:1)), 13 or column M))I'm not even certain it will work or if I'll be able to drag it down to apply to other formulas because it's an array.Any advise appreciated.

 

[jjacks60]

If I'm not explaining my criteria well enough just say.

I can't post images from the file itself as it's confidential.

 

[WarPigl3t]

So you have SCU numbers in column A. A SCU number might be present multiple times in column A. For example, the SCU number 123456 might be in A2, A3, and A5. You want to return the information from the entire row of each SCU number. For example, row 2, 3, and 5. Do you mind if I make a custom function for you? I'm about to go watch Gaurdians of the Galaxy, so I'll make it for you when i get back in 3 hours. I just need to know a few things first. What is the last column that data is in? For example, you have your lookup values in column A. You have data across columns between column A through column M. Is column M as far as it goes? Also I need to know if you have headers. Also I need to know where you are putting this formula. The formula that you stated above is located in what column or what cell? What is the formula suppose to do exactly in plain english please.

 

[WarPigl3t]

Here's how I imagine it. The vba code will update every time something changes in your sheet. So in other words it is an on change event. You will designate 1 cell as your lookup value. Lets say that your lookup cell is A1 and it has the value or SKU number of 123456. VBA will look at cell A1 and find all of the SKU numbers in column A, then display the values of each row where it finds the SKU. This is quite easy. Before I give you this code, are you new to VBA?