Lookup

[Michael Croy]

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

 

[Max]

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,MATCH(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.

 

[Michael Croy]

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]

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

 

[Michael Croy]

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]

In "Master Sheet",
Try in E14:
=IF(COUNT(G2,K2)<2,"",INDEX(Factor!$B$3:$D$53,MATCH(K2,Factor!$A$3:$A$53,0),MATCH(G2,Factor!$B$2:$D$2,0)))

 

[Michael Croy]

Max

Extremely helpful, thanks. Next time I'm in Singapore I'll buy you a beer!


Michael Croy

 

[Max]

Welcome, Michael