Ok, I didn't quite understand the second sheet there, but from how I can
interpret it...
If you have the master as:
A B C
D
1 ITEM NUMBER DESCRIPTION AVG Wght EST
2 4263 CHICKEN BREAST 10.0 P-7085
3 59234 CHICKEN DRUM 10.5 P-72
4 66850 TURKEY HAM 10.0 P-210
5 89222 SLICED BONELESS 25.0 13433
and the order as:
A B C
D E
1 ID CERT_DESC CASES total WEIGHT EST
2 143823 BEEF CHUCK EYE ROLL 9 585 86E
3 143836 BEEF FEET USDA 4 16 562M
4 143563 BEEF FLANK STEAK 2 130 86E
I would add a column between ID and CERT_DESC for the Item Code, then use a
vlookup in the CERT_DESC column:
=vlookup(OrderSheet!$B2,MasterSheet!$1:$65536,2,0)
---------------------------------------------------------------
Where:
OrderSheet! is the name of the sheet with the orders on (remember to include
the ! after the sheet name)
$B2 is the item code you want to look up
MasterSheet! is the name of the master sheet (again, don't forget to include
the !)
$1:$65536 is basically including the whole sheet to search (in Excel 2003)
The 2 is the column number (in this case you are looking up the Description,
so in your sample that is the second column)
The 0 refers to whether you need an exact match. If you need the item codes
to match exactly, keep it as 0, if you want to search for the most similar
item code, change it to 1.
-----------------------------------------------------------
The same would apply for the EST column, although the column number would
instead be 4, because in the master sheet sample, EST is the fourth column.
--------------------------------------------------------------------
For the total Weight column, I would use:
=(vlookup(OrderSheet!$B2,MasterSheet!$1:$65536,3,0)*C2
The vlookup bit is nearly the same, but we have again changed the column
number to match the Weight column.
I have placed the vlookup in brackets, so that at the end we can multiply it
by the number of cases.
Hope this is what you are looking for!