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]

I'd suggest something like this. With your EF Copy sheet defined as you show above, create your Stop 1 sheet to look like this:

	A	B	C	D	E	F	G	H
Stop #	Institution	Description	Pack/Size	Qty	UOM	PO #	Row
MACDONALDS	THICKENER NECTAR FD/BEV	200/4.8GRAM	CS
MACDONALDS	BEAN GREEN GR B *GRADED*	6/#10	CS
MACDONALDS	BEAN VEGETARIAN SCE*GRADED*	6/#10	CS
MACDONALDS	BEET DICED *GRADED* C40118	CS
MACDONALDS	POTATO WHOLE WHITE 60-80 CT C	6/#10	CS
MACDONALDS	YAM CUT FCY C40118	CS
MACDONALDS	MUSHROOM PCS & STEMS INDIAM -	6/#10	CS
MACDONALDS	TOMATO DICED CHO *GRADED*	CS

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"]278[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]16[/TD]

[TD="align: right"]3610151[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]

[TD="align: right"]3610151[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]43261[/TD]
[TD="align: right"]12[/TD]

[TD="align: right"]3610151[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]3610151[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"]43261[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]3610151[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]3610151[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"]43261[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]3610151[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

</tbody>

Stop 1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IF($H2="","",INDEX('EF Copy'!B:B,$H2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF('EF Copy'!$A$2:$A$100=$A$2,ROW($A$2:$A$100)),ROWS($H$2:$H2)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Put the stop number in A2. Then in H2, put that array formula, update the ranges to match your sheet, then confirm with Control+Shift+Enter. Now drag down as far as needed. At this point, you can hide column H if you want. It finds the row numbers of matching rows from EF Copy. I do this one time for efficiency - you really don't want to do the same time-intensive formulas in more than one column if you don't have to. Now put in the B2 formula, and drag across to G and down as far as needed.

Now the nice thing about this is that you can copy these formulas to the other Stop sheets. You just have to change the value in A2. Let us know how this works for you.

 

[DC0429]

I will give it a shot.
Thanks for the info!

 

[DC0429]

Entered these formulas... cells coming up blank.
Triple checked the formulas. Used C+S+E correctly to get the {} brackets.
?? Suggestions ??

 

[Eric W]

Only the H2 formula needs the CSE. If you have ensured that the formula is the same as I created, then the next possibility is that the ranges are different. Start with the H2 formula. Make sure that the ranges are the same (is the stop number on the EF Copy sheet actually in column A?). Is there a value in A2 of the stop sheet? Are the values in EF Copy!A:A text or numeric? Same question for A2? Do you actually start in row 2?

 

[DC0429]

Yes... only H2 = CSE
EF Copy and Stop 1 worksheets are virtual copies of each other. Columns A thru G with row 1 as the title row so all cells match up correctly.
All cells are formatted as "General". This is how they come across from the AS400 query.

 

[Eric W]

It still could be a problem where the values on your EF Copy sheet are text, and the value in the Stop 1!A2 cell is numeric. Even if the cell and/or column is formatted as General, the value in the cell could be text, especially if you populate it from a download. How is the number justified in the A column? Is it on the right or the left of the cell? Numbers will be on the right (like I'd assume your A2 cell is) and text, even if it's a number, will be on the left. If the EF Copy!A column has numbers on the left, you can format the A2 cell as text, then re-enter the 1, and the formula should work. Or we can adjust the formula so that it can handle numbers stored as text.

Let me know if that works, there could be other possibilities.

 

[DC0429]

Both are right justified... I even tried changing the info on the EF Copy by just typing a "1" in the cell. Also tried changing the formatting in the cells for both worksheets from general to text to number.
None of those efforts worked.

 

[Eric W]

Find a cell in column A of EF Copy that has a 1 in it, say A10. Then in A3 of Stop 1 put this formula:

='EF Copy'!A10=A2

If it's FALSE, we still have some kind of format error. If TRUE, then there must be some other problem.

 

[DC0429]

Came back TRUE