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