Hi,

This is fairly complicated because of the titles you are using across the
top of the table on sheet2, but here is an array formula that should do it

=VLOOKUP(D2,Sheet2!$A$2:$F$9,MATCH(B2,VALUE(LEFT(S heet2!C$1:F$1,2)),1)+2,)

D2 is the Add Size, A2:F9 is a portion of the lookup table with titles on
row 1. To make it an array you need to press Shift+Ctrl+Enter to enter it.
Note also, you titles sizes must match exactly.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Christine" wrote:

I have searched the site and keep thinking I've got it, but no luck.
Hopefully all you people who are smarter than me can help :-).

I've got a workbook with 2 worksheets. The first worksheet is called "0109",
and the second worksheet is called "Cost" (no quotes in the name).

On sheet one I need to calculate a column of cells to do 2 things, let's say
Cell B3 can be = on of 4 different values and each of those values lookup to
a table on worksheet 2, then the value would = the corresponding column for
the vlookup. Does that make any sense at all? I'll try and put an example in
below:

Sheet One:
Customer Cntr Term Invoice Ad Size Ad / Job Cost
Edible Arrangements 3 1222 One-Sixth Square
Massage Envy 3 1226 One-Sixth Square
The Design House 3 1234 One-Eighth
The Oaks at Post Road 6 1247 One-Quarter
White Smile USA 6 1239 One-Third Square
Wild Bird Center 12 1241 One-Half Horziontal

Sheet 2:

Size Qty 3 mo. Rate 3 mo. Rate Commission 6 mo. Rates 6 mo. Rate Commission
One-Eighth 2 $305.00 $61.00 $275.00 $55.00
One-Sixth Square 9 $440.00 $88.00 $400.00 $80.00
One-Sixth Vertical 12 $440.00 $88.00 $400.00 $80.00
One-Quarter 5 $545.00 $109.00 $500.00 $100.00
One-Third Square 4 $695.00 $139.00 $635.00 $127.00
One-Third Vertical 2 $695.00 $139.00 $635.00 $127.00
One-Half Horziontal 6 $840.00 $168.00 $765.00 $153.00
One-Half Vertical 1 $840.00 $168.00 $765.00 $153.00

For example, on sheet 1 if Edible Arrangements was a 3 month contract AND a
One-Sixth Square, the calculated value should be $88.00 from sheet 2.

If this makes any sense at all and someone can give me the proper syntax, I
would appreciate it.

Thanks for your help,

Christine