correct syntax for nesting "if", "and", and "vlookup"....if possib

[Christine]

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

 

[Spiky]

You should be able to use a lookup function for this.

First, you need to get all of your descriptions matched up. You can't
do a lookup unless terms match. You want to use the "3" under Contract
Term to match the column headers on Sheet 2. And you want the "One-
Sixth Square" to match to the row headers on Sheet 2. It looks like
the row headers match fine, but you need to find a way to make the "3"
match to something. It can either be used to simply number the columns
(as you suggested), or it can match directly to the words at top,
which are currently "3 mo. Rate". And then this needs to work for all
your possible choices, 3 month, 6 month, etc.

If the above are your actual column/row labels, it should be ok. You
might put in column E:
=VLOOKUP(D2,Sheet2!A1:H100,MATCH(B2&" mo. Rate Commission",Sheet2!
A1:H1,0),FALSE)

 

[Bernard Liengme]

On one sheet enter this starting in A1 (so using A1:B5)

Code Cost
apple 10
banana 20
cherry 30
plum 40

In D1 enter the text 'apple'; in E1 enter =VLOOKUP(D1,A2:B5,2,FALSE)
This should return 10
Change D1 to hold 'cherry' and the formula should give 30

Now Cut and Paste D1:E1 to A1:B1 on the other sheet and examine the formula
in B1
Now you have all you need to solve your problem

If more help needed please give us details of your table (you can 'lie'
about what it contains if it is confidential) but tell use what cells it
occupies
best wishes

 

[Shane Devenshire]

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(Sheet2!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.

 

[Christine]

Thanks for all the help. I worked with all 3 answers and was able to get what
I wanted using the Shane's reply. But the other 2 helped me with other
questions I had also. I appreciate all the help.

Christine