Excel VLookup w/ Multiple Values AND Results

[DC0429]

Needing some help please.
Posting this because I could not find a similar answeranywhere.

I have six worksheets… EF Copy, Stop 1, Stop 2, etc toStop 5
On the EF Copy worksheet is a download from our AS400 a deliverylist for a particular delivery route.
Worksheet shows Stop #, Customer, Product Description,Pack Size (of product), Quantity, Unit of Measure, and Customer PO number incolumns A thru G.

Each stop can have 1 to 100+ rows of items.

I need a formula so that worksheet Stop 1 only has Stop 1info, Stop 2 only Stop 2 info, and so on.

Got this working now but not well. Worksheet for stop 1 is fine… but the info onworksheet Stop 2 starts in the row where the info on worksheet Stop 1 ends,

I am sure I need an array formula w/ much more than justa normal VLookup.

Worksheet EFCopy (with ALL route info from the AS400 query):

[TABLE="width: 773"]
<tbody>[TR]
[TD]Stop #[/TD]
[TD]Institution[/TD]
[TD]Description[/TD]
[TD]Pack/Size[/TD]
[TD]Qty[/TD]
[TD]UOM[/TD]
[TD]PO #[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]MACDONALDS[/TD]
[TD]THICKENER NECTAR FD/BEV[/TD]
[TD]200/4.8GRAM[/TD]
[TD="align: right"]4[/TD]
[TD]CS[/TD]
[TD="align: right"]278[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]MACDONALDS[/TD]
[TD]BEAN GREEN GR B *GRADED*[/TD]
[TD]6/#10[/TD]
[TD="align: right"]16[/TD]
[TD]CS[/TD]
[TD="align: right"]3610151[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]MACDONALDS[/TD]
[TD]BEAN VEGETARIAN SCE*GRADED*[/TD]
[TD]6/#10[/TD]
[TD="align: right"]10[/TD]
[TD]CS[/TD]
[TD="align: right"]3610151[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]MACDONALDS[/TD]
[TD]BEET DICED *GRADED* C40118[/TD]
[TD]6/10[/TD]
[TD="align: right"]12[/TD]
[TD]CS[/TD]
[TD="align: right"]3610151[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]MACDONALDS[/TD]
[TD]POTATO WHOLE WHITE 60-80 CT C[/TD]
[TD]6/#10[/TD]
[TD="align: right"]6[/TD]
[TD]CS[/TD]
[TD="align: right"]3610151[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]MACDONALDS[/TD]
[TD]YAM CUT FCY C40118[/TD]
[TD]6/10[/TD]
[TD="align: right"]6[/TD]
[TD]CS[/TD]
[TD="align: right"]3610151[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]MACDONALDS[/TD]
[TD]MUSHROOM PCS & STEMS INDIAM -[/TD]
[TD]6/#10[/TD]
[TD="align: right"]2[/TD]
[TD]CS[/TD]
[TD="align: right"]3610151[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]MACDONALDS[/TD]
[TD]TOMATO DICED CHO *GRADED*[/TD]
[TD]6/10[/TD]
[TD="align: right"]10[/TD]
[TD]CS[/TD]
[TD="align: right"]3610151[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]HARDLEES[/TD]
[TD]BROCCOLI FROZEN[/TD]
[TD]1/20#[/TD]
[TD="align: right"]4[/TD]
[TD]CS[/TD]
[TD="align: right"]905[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]HARDLEES[/TD]
[TD]VEGETABLE MIX FLORENTINE CALIF[/TD]
[TD]20/LB[/TD]
[TD="align: right"]40[/TD]
[TD]CS[/TD]
[TD="align: right"]905[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]HARDLEES[/TD]
[TD]POTATO ROUND[/TD]
[TD]6/5 LB[/TD]
[TD="align: right"]50[/TD]
[TD]CS[/TD]
[TD="align: right"]905[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]HARDLEES[/TD]
[TD]POTATO FRENCH FRY STRAIGHT[/TD]
[TD]6/5 LB[/TD]
[TD="align: right"]3[/TD]
[TD]CS[/TD]
[TD="align: right"]905[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]HARDLEES[/TD]
[TD]MARGARINE CUPS SOY OIL 5GM[/TD]
[TD]900/5 GM[/TD]
[TD="align: right"]133[/TD]
[TD]CS[/TD]
[TD="align: right"]911[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]HARDLEES[/TD]
[TD]MARGARINE CUPS SOY OIL 5GM[/TD]
[TD]900/5 GM[/TD]
[TD="align: right"]133[/TD]
[TD]CS[/TD]
[TD="align: right"]911[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]HARDLEES[/TD]
[TD]CHEESE AMERICAN BLD 160 SLC (I[/TD]
[TD]1/5#[/TD]
[TD="align: right"]100[/TD]
[TD]EA[/TD]
[TD="align: right"]963[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]WINDYS[/TD]
[TD]BROCCOLI FROZEN[/TD]
[TD]1/20#[/TD]
[TD="align: right"]50[/TD]
[TD]CS[/TD]
[TD="align: right"]904[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]WINDYS[/TD]
[TD]BROCCOLI FROZEN[/TD]
[TD]1/20#[/TD]
[TD="align: right"]50[/TD]
[TD]CS[/TD]
[TD="align: right"]904[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]WINDYS[/TD]
[TD]VEGETABLE MIX FLORENTINE CALIF[/TD]
[TD]20/LB[/TD]
[TD="align: right"]33[/TD]
[TD]CS[/TD]
[TD="align: right"]904[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]WINDYS[/TD]
[TD]POTATO ROUND[/TD]
[TD]6/5 LB[/TD]
[TD="align: right"]100[/TD]
[TD]CS[/TD]
[TD="align: right"]904[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]WINDYS[/TD]
[TD]POTATO FRENCH FRY STRAIGHT[/TD]
[TD]6/5 LB[/TD]
[TD="align: right"]30[/TD]
[TD]CS[/TD]
[TD="align: right"]904[/TD]
[/TR]
</tbody>[/TABLE]






Worksheet Stop 1:

[TABLE="width: 800"]
<tbody>[TR]
[TD]Institution[/TD]
[TD]Stop #[/TD]
[TD]Description[/TD]
[TD]Pack/Size[/TD]
[TD]Qty[/TD]
[TD]UOM[/TD]
[TD]PO #[/TD]
[/TR]
[TR]
[TD]MACDONALDS[/TD]
[TD="align: right"]1[/TD]
[TD]THICKENER NECTAR FD/BEV[/TD]
[TD]200/4.8GRAM[/TD]
[TD="align: right"]4[/TD]
[TD]CS[/TD]
[TD]278[/TD]
[/TR]
[TR]
[TD]MACDONALDS[/TD]
[TD="align: right"]1[/TD]
[TD]BEAN GREEN GR B *GRADED* C401[/TD]
[TD]6/#10[/TD]
[TD="align: right"]16[/TD]
[TD]CS[/TD]
[TD]3610151[/TD]
[/TR]
[TR]
[TD]MACDONALDS[/TD]
[TD="align: right"]1[/TD]
[TD]BEAN VEGETARIAN SCE*GRADED* C4[/TD]
[TD]6/#10[/TD]
[TD="align: right"]10[/TD]
[TD]CS[/TD]
[TD]3610151[/TD]
[/TR]
[TR]
[TD]MACDONALDS[/TD]
[TD="align: right"]1[/TD]
[TD]BEET DICED *GRADED* C40118[/TD]
[TD]6/10[/TD]
[TD="align: right"]12[/TD]
[TD]CS[/TD]
[TD]3610151[/TD]
[/TR]
[TR]
[TD]MACDONALDS[/TD]
[TD="align: right"]1[/TD]
[TD]POTATO WHOLE WHITE 60-80 CT C[/TD]
[TD]6/#10[/TD]
[TD="align: right"]6[/TD]
[TD]CS[/TD]
[TD]3610151[/TD]
[/TR]
[TR]
[TD]MACDONALDS[/TD]
[TD="align: right"]1[/TD]
[TD]YAM CUT FCY C40118[/TD]
[TD]6/10[/TD]
[TD="align: right"]6[/TD]
[TD]CS[/TD]
[TD]3610151[/TD]
[/TR]
[TR]
[TD]MACDONALDS[/TD]
[TD="align: right"]1[/TD]
[TD]MUSHROOM PCS & STEMS INDIAM -[/TD]
[TD]6/#10[/TD]
[TD="align: right"]2[/TD]
[TD]CS[/TD]
[TD]3610151[/TD]
[/TR]
[TR]
[TD]MACDONALDS[/TD]
[TD="align: right"]1[/TD]
[TD]TOMATO DICED CHO *GRADED* C401[/TD]
[TD]6/10[/TD]
[TD="align: right"]10[/TD]
[TD]CS[/TD]
[TD]3610151[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>

<strike></strike>

Thanks for any assistance!

 

[Eric W]

OK, so it finds the right stops. And you're not getting any row numbers listed in column H? Column H is formatted as General? You already said you checked for the CSE brackets. The formula is entered in H2 of the Stop 1 sheet? Then the formula is copied and pasted down the column from H3 to H100 (or as far as needed)? About the only other thing I can think of is making sure that the ranges are correct:

=IFERROR(SMALL(IF('EF Copy'!$A$2:$A$100=$A$2,ROW('EF Copy'!$A$2:$A$100)),ROWS($H$2:$H2)),"")

Did you expand your range on EF Copy (red) to extend to the maximum possible row? Maybe 1000 instead of 100? Is A2 (green) the cell with the stop number? Is the row number (blue) the cell where you entered the formula? Are all the $ signs in the right places?