Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
I'm trying to solve what I think is a complex formula.
To start with, I have a table (call it table 1) that has 'Age' in the column, and the top row has another variable. Then there are different values in the cells depending on the 'age' selected and the other variable. On a separate tab, I enter the age and the other variable into two different cells. In a third cell, I want to put a formula that goes to table 1, uses the age and the other variable, and returns with the value. How do I program that function into the third cell? Michael Croy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
One guess is that you could try a dual criteria index/match (array-entered)
Assume Age & Var1 is listed in Sheet1's cols A & B, data from row2 down. Assume you have 3 other cols of interest in adjacent cols C to E corresponding to the 2 key cols A and B In Sheet2, Assuming the paired inputs of Age & Var1 will be input in A2:B2 down, Paste this in C2's formula bar, then array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(COUNTA($A2:$B2)<2,"",INDEX(Sheet1!C$2:C$100,MA TCH(1,(Sheet1!$A$2:$A$100=$A2)*(Sheet1!$B$2:$B$100 =$B2),0))) Copy C2 across to E2, fill down as far as required. This returns the required results from Sheet1's cols C to E. Adapt the ranges to suit the extents of data in Sheet1. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Michael Croy" wrote: I'm trying to solve what I think is a complex formula. To start with, I have a table (call it table 1) that has 'Age' in the column, and the top row has another variable. Then there are different values in the cells depending on the 'age' selected and the other variable. On a separate tab, I enter the age and the other variable into two different cells. In a third cell, I want to put a formula that goes to table 1, uses the age and the other variable, and returns with the value. How do I program that function into the third cell? Michael Croy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
Hi Max
Thanks, I think I'm getting close. I need to better describe my issue: One sheet 1, I want to enter the number of payments (3 choices) in cell G7. Also on sheet 1, I want to enter the age in cell K7. Then I'm going to calculate a payment in cell E19. To calculate the payment, I need to multiply a total price (cell E17) by the result of the lookup. The lookup is on sheet 2. The table has Age in column A. Row 1 B, C and D have the three payment choices. I want the formula in cell E19 of sheet 1 to look at the table on sheet 2, and return a value based on the age and payment information listed in sheet 1's K7 and G7 cells. I seem to be able to look up and populate other tables, but not have a variable that changes based on the input I make into the age and payment number cells. Can you help with that? "Max" wrote: One guess is that you could try a dual criteria index/match (array-entered) Assume Age & Var1 is listed in Sheet1's cols A & B, data from row2 down. Assume you have 3 other cols of interest in adjacent cols C to E corresponding to the 2 key cols A and B In Sheet2, Assuming the paired inputs of Age & Var1 will be input in A2:B2 down, Paste this in C2's formula bar, then array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(COUNTA($A2:$B2)<2,"",INDEX(Sheet1!C$2:C$100,MA TCH(1,(Sheet1!$A$2:$A$100=$A2)*(Sheet1!$B$2:$B$100 =$B2),0))) Copy C2 across to E2, fill down as far as required. This returns the required results from Sheet1's cols C to E. Adapt the ranges to suit the extents of data in Sheet1. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Michael Croy" wrote: I'm trying to solve what I think is a complex formula. To start with, I have a table (call it table 1) that has 'Age' in the column, and the top row has another variable. Then there are different values in the cells depending on the 'age' selected and the other variable. On a separate tab, I enter the age and the other variable into two different cells. In a third cell, I want to put a formula that goes to table 1, uses the age and the other variable, and returns with the value. How do I program that function into the third cell? Michael Croy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
Tough for me to visualize your set-up. And you seem to have expanded the
scope of your original query. Can you upload your sample* and post a link to it here? *desensitized as appropriate You could use: http://www.freefilehosting.net/ Copy the direct link which is generated after you upload your sample, then paste it into your reply here -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Michael Croy" wrote in message ... Hi Max Thanks, I think I'm getting close. I need to better describe my issue: One sheet 1, I want to enter the number of payments (3 choices) in cell G7. Also on sheet 1, I want to enter the age in cell K7. Then I'm going to calculate a payment in cell E19. To calculate the payment, I need to multiply a total price (cell E17) by the result of the lookup. The lookup is on sheet 2. The table has Age in column A. Row 1 B, C and D have the three payment choices. I want the formula in cell E19 of sheet 1 to look at the table on sheet 2, and return a value based on the age and payment information listed in sheet 1's K7 and G7 cells. I seem to be able to look up and populate other tables, but not have a variable that changes based on the input I make into the age and payment number cells. Can you help with that? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
http://www.freefilehosting.net/download/3eec0
<a href="http://www.freefilehosting.net/files/3eec0"Croy Test Spreadsheet.xls</a Croy Test Spreadsheet.xls Max Links attached. I'm trying to calculate a value in cell e14. I want that number to be calculated based on the numbers entered in cells G2 and K2 and then the number on the factor tab. For example, for the 2 numbers listed in G2 and K2, the value should be .025 (highlighted in yellow). That factor should then be multiplied by the number in E12. The complication is I want to be able to change the numbers in G2 and/or K2, and the formula will go to the correct cell on the factor tab and return the correct factor to the formula in cell e 14. Let me know if any of that makes sense. Thanks. "Max" wrote: Tough for me to visualize your set-up. And you seem to have expanded the scope of your original query. Can you upload your sample* and post a link to it here? *desensitized as appropriate You could use: http://www.freefilehosting.net/ Copy the direct link which is generated after you upload your sample, then paste it into your reply here -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Michael Croy" wrote in message ... Hi Max Thanks, I think I'm getting close. I need to better describe my issue: One sheet 1, I want to enter the number of payments (3 choices) in cell G7. Also on sheet 1, I want to enter the age in cell K7. Then I'm going to calculate a payment in cell E19. To calculate the payment, I need to multiply a total price (cell E17) by the result of the lookup. The lookup is on sheet 2. The table has Age in column A. Row 1 B, C and D have the three payment choices. I want the formula in cell E19 of sheet 1 to look at the table on sheet 2, and return a value based on the age and payment information listed in sheet 1's K7 and G7 cells. I seem to be able to look up and populate other tables, but not have a variable that changes based on the input I make into the age and payment number cells. Can you help with that? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
In "Master Sheet",
Try in E14: =IF(COUNT(G2,K2)<2,"",INDEX(Factor!$B$3:$D$53,MATC H(K2,Factor!$A$3:$A$53,0),MATCH(G2,Factor!$B$2:$D$ 2,0))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Michael Croy" wrote in message ... http://www.freefilehosting.net/download/3eec0 Max Links attached. I'm trying to calculate a value in cell e14. I want that number to be calculated based on the numbers entered in cells G2 and K2 and then the number on the factor tab. For example, for the 2 numbers listed in G2 and K2, the value should be .025 (highlighted in yellow). That factor should then be multiplied by the number in E12. The complication is I want to be able to change the numbers in G2 and/or K2, and the formula will go to the correct cell on the factor tab and return the correct factor to the formula in cell e 14. Let me know if any of that makes sense. Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
Max
Extremely helpful, thanks. Next time I'm in Singapore I'll buy you a beer! Michael Croy "Max" wrote: In "Master Sheet", Try in E14: =IF(COUNT(G2,K2)<2,"",INDEX(Factor!$B$3:$D$53,MATC H(K2,Factor!$A$3:$A$53,0),MATCH(G2,Factor!$B$2:$D$ 2,0))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Michael Croy" wrote in message ... http://www.freefilehosting.net/download/3eec0 Max Links attached. I'm trying to calculate a value in cell e14. I want that number to be calculated based on the numbers entered in cells G2 and K2 and then the number on the factor tab. For example, for the 2 numbers listed in G2 and K2, the value should be .025 (highlighted in yellow). That factor should then be multiplied by the number in E12. The complication is I want to be able to change the numbers in G2 and/or K2, and the formula will go to the correct cell on the factor tab and return the correct factor to the formula in cell e 14. Let me know if any of that makes sense. Thanks. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
Welcome, Michael
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Michael Croy" wrote in message ... Max Extremely helpful, thanks. Next time I'm in Singapore I'll buy you a beer! Michael Croy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |