How do I create a lookup using layered parameters
 

I am attempting to setup a calculator which will calculate property value
based on loan amounts using the equation: PV = LA/max % financed. The
problem is % is based off many factors. I have the loan tables setup with
all the possible percents based on loan amount. This information is housed
on a separate worksheet in the same file.

I am having trouble finding the right lookup funtion to first look for the
right table and then find the right percent to use from the table based on
loan amount. There are 36 different tables based on property and loan type.
I need the function to find the right table to go to based on information
entered by the user then pluck the right percent for LA to input into the
equation to calculate PV.

How do I create a lookup using layered parameters
 

My two cents: perhaps you can use your paramaters to determine the sheet name
and then use vlookup to determine the sheet from which the data will be
pulled (it's hard to be more specific w/o more specific info).

Such as:
=VLOOKUP($A11,A7&!$C$6:$I$52,I$9,FALSE)

where A7 is the name of the worksheet based on different criteria (through
an IF statement).

Just my thoughts on how I would go about doing this sort of thing. Also,
MATCH is sometimes helpful within VLOOKUP when dealing with multiple factors
(in the col_index_num slot).

If you'd like more specific help, be as specific as you can be.

Hope this helps!
--
-SA


"Eric" wrote:

I am attempting to setup a calculator which will calculate property value
based on loan amounts using the equation: PV = LA/max % financed. The
problem is % is based off many factors. I have the loan tables setup with
all the possible percents based on loan amount. This information is housed
on a separate worksheet in the same file.

I am having trouble finding the right lookup funtion to first look for the
right table and then find the right percent to use from the table based on
loan amount. There are 36 different tables based on property and loan type.
I need the function to find the right table to go to based on information
entered by the user then pluck the right percent for LA to input into the
equation to calculate PV.

How do I create a lookup using layered parameters
 

Columns A - F contain the parameters which need to be checked. G - J have
the % which needs to be pulled based on the matching parameters in A - F. I
know Vlookup can only look at one column when pulling corresponding info. I
need a way to verify all columns a - f then pulling a corresponding % in g -
j.

I would appreciate anyones help with this.

Thanks,

"StumpedAgain" wrote:

My two cents: perhaps you can use your paramaters to determine the sheet name
and then use vlookup to determine the sheet from which the data will be
pulled (it's hard to be more specific w/o more specific info).

Such as:
=VLOOKUP($A11,A7&!$C$6:$I$52,I$9,FALSE)

where A7 is the name of the worksheet based on different criteria (through
an IF statement).

Just my thoughts on how I would go about doing this sort of thing. Also,
MATCH is sometimes helpful within VLOOKUP when dealing with multiple factors
(in the col_index_num slot).

If you'd like more specific help, be as specific as you can be.

Hope this helps!
--
-SA


"Eric" wrote:

I am attempting to setup a calculator which will calculate property value
based on loan amounts using the equation: PV = LA/max % financed. The
problem is % is based off many factors. I have the loan tables setup with
all the possible percents based on loan amount. This information is housed
on a separate worksheet in the same file.

I am having trouble finding the right lookup funtion to first look for the
right table and then find the right percent to use from the table based on
loan amount. There are 36 different tables based on property and loan type.
I need the function to find the right table to go to based on information
entered by the user then pluck the right percent for LA to input into the
equation to calculate PV.