Help - VLookup formula written, but need to return data on multiple rows

[thelmacottonwood]

Hey all. New to the forum. Glad to have found this site. Thanks in advance for help. So I have a file with two spreadsheets, the first captures customer information (CustomerList) and the second is an assembly order (AssemblyOrder) generated from the customer info. I was able to successfully write formulas that return one row of customer data per customer on the Assembly Order form, but I need to be able to return multiple rows of data for each customer.

When I enter a customer number - say 1 - into AssemblyOrder E7, I need to return data from every row in CustomerList that matches customer number 1. For instance in the AssemblyOrder B19 I entered =IF($E$7="","",VLOOKUP($E$7,CustomerList!$A$2:$AG$130,2)). Row 19 returns an order (CustomerList row 2) for customer number 1 identified in AssemblyOrder E7.

How do I get AssemblyOrder row 20 to return the next order (CustomerList row 3) for customer number 1? I'v attached the spreadsheet. Thanks so much. Hopefully I've explained my quandary clearly enough. Thanks so much for any assistance you can offer!

[ptm0412]

Hi cottonwood,
If your Customer list is sorted by Customer_Number, we can use a dynamic Name like this:
Customer =OFFSET(CustomerList!$A$1:$X$1,MATCH(AssemblyOrder!$E$7,CustomerList!$A$2:$A$100,0),,COUNTIF(CustomerList!$A$2:$A$100,AssemblyOrder!$E$7),)
After that we can use Index to get all rows.

[thelmacottonwood]

Thanks so much for the quick response!

I think I'm missing something, though. Where is the following formula located?

=OFFSET(CustomerList!$A$1:$X$1,MATCH(AssemblyOrder!$E$7,CustomerList!$A$2:$A$100,0),,COUNTIF(CustomerList!$A$2:$A$100,AssemblyOrder!$E$7),)

Because when I type your INDEX formula into my database (an exact replica of the attached), the cells come up empty. I recognize that I need the formula above, just not sure where. Thanks again.

[thelmacottonwood]

Again, thanks for your help. Now I have the formula that references the dynamic Name Range.

=IFERROR(INDEX(Customer,ROW()-18,A$17),"")

"Customer" has been defined as a dynamic name referencing the following formula.

=OFFSET(CustomerList!$A$1:$X$1,MATCH(AssemblyOrder!$E$7,CustomerList!$A$2:$A$100,0),,COUNTIF(CustomerList!$A$2:$A$100,AssemblyOrder!$E$7),)

I've tried to recreate the formulas in my own workbook but for some reason the array "Customer" isn't being recognized. When I'm recreating the =IFERROR(INDEX(Customer,ROW()-18,A$17),"") formula is there some sort of trick to get it to recognize the dynamic Name? I've tried to Insert/Name/Paste (the array is clearly defined), I've tried to simply type the name of the dynamic Name. What am I missing?

Thanks for your help. I've been trying to figure this out but am at a standstill.

[ptm0412]

I've tried to recreate the formulas in my own workbook but for some reason the array "Customer" isn't being recognized. When I'm recreating the =IFERROR(INDEX(Customer,ROW()-18,A$17),"") formula is there some sort of trick to get it to recognize the dynamic Name? I've tried to Insert/Name/Paste (the array is clearly defined), I've tried to simply type the name of the dynamic Name. What am I missing?
Thanks for your help. I've been trying to figure this out but am at a standstill.

Would you do like this step by step:
- Go to Insert/ Name/ Define
- Click to select the Name "Customer"
- Click on the "Refered to" box as if editing
- If the name range is defined correctly, Excel will bring you to the range that it refered to. (See the image)

NameReferedTo.jpg

- If not, you have to test again:

=OFFSET(CustomerList!$A$1:$X$1,MATCH(AssemblyOrder!$E$7,CustomerList!$A$2:$A$100,0), , COUNTIF(CustomerList!$A$2:$A$100,AssemblyOrder!$E$7),)

i. Make sure that AssemblyOrder!$E$7 is the cell contains Customer number
ii. Make sure that the customer number in cell E7 exists in Customer list
iii. Make sure that E7 and column A in sheet CustomerList is in the same format (number or text)
iv. If E7 > 100, Change 100 in the Range Name to 1000 or more

If you do not know why, let me see your real workbook.