Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
correct syntax for nesting "if", "and", and "vlookup"....if possib
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
correct syntax for nesting "if", "and", and "vlookup"....ifpossib
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
correct syntax for nesting "if", "and", and "vlookup"....if possib
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Christine" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
correct syntax for nesting "if", "and", and "vlookup"....if possib
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
correct syntax for nesting "if", "and", and "vlookup"....if po
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 "Shane Devenshire" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) |