Formula needed to return multiple results based on one reference

Must say that I've browsed this forum for quite some time and loved it so much I registered in the event I could help somebody out (hasn't happened yet) or my fear...I needed help.

Well like many posts have started out...I've searched, read, searched, read, and still cannot find something close enough to my need so here goes my first post.

The attachment is setup as such:

Sheet "Main" has the following:

ITEMS in cells A2:A16
ORDER #'s in cells B2:B16
TOTAL QTY in cells C2:C16

Sheet "Data" has the raw reporting sales order data, hypothetically extracted from our company database.
Items in column A, Order # in column B for items, and qty per item/order# in column C.

On the Main sheet I have used the "sumif" formula to calculate the total qty on order for the referenced item, no issues. How in the world can I use VLOOKUP, or something similar to report back (Column B is my intention) each individual ORDER #?

So if I have Toys on order #'s 1,2, and 3...I would like to see it show "1, 2, 3"

Please help!

Hi

One way is to use a User Defined Function (UDF).

Put the code below into a general module in the relevant workbook.

Please Login or Register  to view this content.

Then using your example file, enter =myfunc(DATA!$A$2:$A$61,MAIN!A2) in Main!B2 and copy down to B16.

HTH

rylo

Thank you for the feedback guys - working on this today.

This is working, but I have a quick question. My actual sheet I'm working with has spaces between items, so as I copy the formula down it returns #VALUE! when the cell in column A is blank. Is there some type of conditional formatting I can use to "hide" this result? In the past I've used White font w/ no fill when wanting to make zeros disappear, but I don't know how to do it in this case.

Posed the question way too soon, sorry - forgot about More Rules/New Rules.

Originally Posted by alansidman

Here is a UDF and a spreadsheet with a demo on how to set it up.

So I am having decent success understanding this, but I'm running into one small issue. I've pasted the code into a module and used the formula. However after I fill the formula down the column it does not formulate...UNTIL I go to my Table Array and make a small edit. This edit could be as simple as changing a "Q" to a "F". Once I make that change everything works. Is there something I'm missing?

I guess it seems like I'm not "starting" the VBA or something...

Calculation method checked, tried Manual/calculating and Automatic....Attached I believe should work as an example