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

jjacks60

New Member
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.

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
If I'm not explaining my criteria well enough just say.

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

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.

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?

Replies
8
Views
654
Replies
3
Views
443
Replies
3
Views
775
Replies
12
Views
444
Replies
2
Views
1K

Forum statistics

1,221,052
Messages
6,157,632
Members
451,426
Latest member
VinnyDoesntKnowExcelCode

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back