Which functions do I use
 

I have looked through all the other posts and found some great answers but
not exactly what I need, so if anyone can solve this for me that would be
great.

Thanks in advance

I have the follow data and want to return the result based on 3 lists.
d2 contains dealer or branch in pick list
d3 contains 10% cancel, 5% cancel or 10% fmv in a pick list.
d4 months in a pick list
I would like to get the correct rate based on the selections.
ie, dealer + 10% fmv + 36 mth would return .03108
or branch + 10% fmv + 36 myhs would return .03280
and any combination of.



Dealer Months 10% Cancel5% Cancel10% FMV
24 0.05660 0.05126 0.04426
36 0.04318 0.03755 0.03108
39 0.04118 0.03546 0.02905
48 0.03678 0.03083 0.02452
51 0.03570 0.02967 0.02336
60 0.03318 0.02691 0.02061
63 0.03252 0.02618 0.01987
Branch 24 0.05747 0.05126 0.04595
36 0.04411 0.03755 0.03280
39 0.04212 0.03546 0.03079
48 0.03777 0.03083 0.02629
51 0.03670 0.02967 0.02514
60 0.03422 0.02691 0.02242
63 0.03358 0.02618 0.02169

Which functions do I use
 

Name some functions. Use Data Validation (I think you did with the drop down
lists).

I highlighted from the 24 month under dealer, to the value for 10%FMV for 63
Months (0.01987) and named the range Tbl_Dealer (menu Insert|Name|Define).
I then highlighted from teh 24 month under Branch, to the value for 10%FMV
for 63 months (0.02169), and named the range Tbl_Branch.
Assuming your data validation is lists for all 3 cells D2, D3, D4, and with
possibilities as follows:
D2: Dealer,Branch
D3: 10% Cancel,5% Cancel,10% FMV
D4: 24,36,39,48,51,60,63

I then used the following formula:
=IF(OR(D2="",D3="",D4=""),"",VLOOKUP(D4,INDIRECT(" Tbl_"&D2),VLOOKUP(D3,{"10%
Cancel",2;"5% Cancel",3;"10% FMV",4},2,FALSE),FALSE))

Hope this helps!


--
John C


"Papa P" wrote:

I have looked through all the other posts and found some great answers but
not exactly what I need, so if anyone can solve this for me that would be
great.

Thanks in advance

I have the follow data and want to return the result based on 3 lists.
d2 contains dealer or branch in pick list
d3 contains 10% cancel, 5% cancel or 10% fmv in a pick list.
d4 months in a pick list
I would like to get the correct rate based on the selections.
ie, dealer + 10% fmv + 36 mth would return .03108
or branch + 10% fmv + 36 myhs would return .03280
and any combination of.



Dealer Months 10% Cancel5% Cancel10% FMV
24 0.05660 0.05126 0.04426
36 0.04318 0.03755 0.03108
39 0.04118 0.03546 0.02905
48 0.03678 0.03083 0.02452
51 0.03570 0.02967 0.02336
60 0.03318 0.02691 0.02061
63 0.03252 0.02618 0.01987
Branch 24 0.05747 0.05126 0.04595
36 0.04411 0.03755 0.03280
39 0.04212 0.03546 0.03079
48 0.03777 0.03083 0.02629
51 0.03670 0.02967 0.02514
60 0.03422 0.02691 0.02242
63 0.03358 0.02618 0.02169

Which functions do I use
 

John
Thanks for the super fast response which worked perfectly, I would never
have got there on my own and you have saved me hours of grief.

"John C" wrote:

Name some functions. Use Data Validation (I think you did with the drop down
lists).

I highlighted from the 24 month under dealer, to the value for 10%FMV for 63
Months (0.01987) and named the range Tbl_Dealer (menu Insert|Name|Define).
I then highlighted from teh 24 month under Branch, to the value for 10%FMV
for 63 months (0.02169), and named the range Tbl_Branch.
Assuming your data validation is lists for all 3 cells D2, D3, D4, and with
possibilities as follows:
D2: Dealer,Branch
D3: 10% Cancel,5% Cancel,10% FMV
D4: 24,36,39,48,51,60,63

I then used the following formula:
=IF(OR(D2="",D3="",D4=""),"",VLOOKUP(D4,INDIRECT(" Tbl_"&D2),VLOOKUP(D3,{"10%
Cancel",2;"5% Cancel",3;"10% FMV",4},2,FALSE),FALSE))

Hope this helps!


--
John C


"Papa P" wrote:

I have looked through all the other posts and found some great answers but
not exactly what I need, so if anyone can solve this for me that would be
great.

Thanks in advance

I have the follow data and want to return the result based on 3 lists.
d2 contains dealer or branch in pick list
d3 contains 10% cancel, 5% cancel or 10% fmv in a pick list.
d4 months in a pick list
I would like to get the correct rate based on the selections.
ie, dealer + 10% fmv + 36 mth would return .03108
or branch + 10% fmv + 36 myhs would return .03280
and any combination of.



Dealer Months 10% Cancel5% Cancel10% FMV
24 0.05660 0.05126 0.04426
36 0.04318 0.03755 0.03108
39 0.04118 0.03546 0.02905
48 0.03678 0.03083 0.02452
51 0.03570 0.02967 0.02336
60 0.03318 0.02691 0.02061
63 0.03252 0.02618 0.01987
Branch 24 0.05747 0.05126 0.04595
36 0.04411 0.03755 0.03280
39 0.04212 0.03546 0.03079
48 0.03777 0.03083 0.02629
51 0.03670 0.02967 0.02514
60 0.03422 0.02691 0.02242
63 0.03358 0.02618 0.02169

Which functions do I use
 

No problem, thanks for the feedback. And don't forget to check 'yes'. :-D
--
John C


"Papa P" wrote:

John
Thanks for the super fast response which worked perfectly, I would never
have got there on my own and you have saved me hours of grief.

"John C" wrote:

Name some functions. Use Data Validation (I think you did with the drop down
lists).

I highlighted from the 24 month under dealer, to the value for 10%FMV for 63
Months (0.01987) and named the range Tbl_Dealer (menu Insert|Name|Define).
I then highlighted from teh 24 month under Branch, to the value for 10%FMV
for 63 months (0.02169), and named the range Tbl_Branch.
Assuming your data validation is lists for all 3 cells D2, D3, D4, and with
possibilities as follows:
D2: Dealer,Branch
D3: 10% Cancel,5% Cancel,10% FMV
D4: 24,36,39,48,51,60,63

I then used the following formula:
=IF(OR(D2="",D3="",D4=""),"",VLOOKUP(D4,INDIRECT(" Tbl_"&D2),VLOOKUP(D3,{"10%
Cancel",2;"5% Cancel",3;"10% FMV",4},2,FALSE),FALSE))

Hope this helps!


--
John C


"Papa P" wrote:

I have looked through all the other posts and found some great answers but
not exactly what I need, so if anyone can solve this for me that would be
great.

Thanks in advance

I have the follow data and want to return the result based on 3 lists.
d2 contains dealer or branch in pick list
d3 contains 10% cancel, 5% cancel or 10% fmv in a pick list.
d4 months in a pick list
I would like to get the correct rate based on the selections.
ie, dealer + 10% fmv + 36 mth would return .03108
or branch + 10% fmv + 36 myhs would return .03280
and any combination of.



Dealer Months 10% Cancel5% Cancel10% FMV
24 0.05660 0.05126 0.04426
36 0.04318 0.03755 0.03108
39 0.04118 0.03546 0.02905
48 0.03678 0.03083 0.02452
51 0.03570 0.02967 0.02336
60 0.03318 0.02691 0.02061
63 0.03252 0.02618 0.01987
Branch 24 0.05747 0.05126 0.04595
36 0.04411 0.03755 0.03280
39 0.04212 0.03546 0.03079
48 0.03777 0.03083 0.02629
51 0.03670 0.02967 0.02514
60 0.03422 0.02691 0.02242
63 0.03358 0.02618 0.02169

Which functions do I use
 

Here's another option which uses index/match/offset to deliver the goods ...

Illustrated in this sample:
http://www.freefilehosting.net/download/3k2jd
Extract from table based on 3 input params.xls

Assuming source reference table in A10:E24,
with the text "Dealer" placed into A11 (instead of in A10)
(this makes the table's structure symmetric & easier to read from)

Then in say, D6:
=IF(COUNTA(D2:D4)<3,"",
INDEX(OFFSET(A11:A24,,MATCH(D3,A10:E10,0)-1),
MATCH(D2,A10:A24,0)+MATCH(D4,OFFSET(A10,MATCH(D2,A 10:A24,0)-1,1,7),0)-2))
will return the result from the reference table
based on input DV selections made in D2:D4
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---