Ok, I've just noticed not everything really fit on there...

A is supposed to be Item Number
B is supposed to be Description
C is supposed to be Avg Weight
D is supposed to be EST

A is supposed to be ID
B is supposed to be CERT_DESC
C is supposed to be CASES
D is supposed to be Total WEIGHT
E is supposed to be EST


"Amy Brooks" wrote:

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)
---------------------------------------------------------------
Whe
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!


"conditional format if cell contains word" wrote:

The master looks like this but much larger.
ITEM NUMBER DESCRIPTION AVG Wght EST
4263 CHICKEN BREAST 10.0 P-7085
59234 CHICKEN DRUM 10.5 P-72
66850 TURKEY HAM 10.0 P-210
89222 SLICED BONELESS 25.0 13433

The order looks like this:
Item code Description Cases total
weight EST
143823 9
143836 10
143563 5
143875 6


ID CERT_DESC CASES total WEIGHT EST
143823 BEEF CHUCK EYE ROLL 9 585 86E
143836 BEEF FEET USDA 4 16 562M
143563 BEEF FLANK STEAK 2 130 86E


So I need the descriptions and ESTs to be filled in with exact matches and
need the weight to be the avg weight from the master multiplied by the cases
from the order.