Auto-update cells based on a cell drop-down list and a table

[Castillb]

Hi,

I have a drop down list in cell B2 that allows the user to select different deductible values (1,000, 2,000, etc...). Could somebody show me how to go about auto-populating Column E with the values that are in the table, based on the deductible amount that is selected from the drop down and the table number that is column D? Could this be done using a VLOOKUP or an IF Statement, or both? Thank you for time and help.

Column Column
D E

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl77, width: 64"]Table[/TD]
[TD="class: xl73, width: 64"] Ded[/TD]
[/TR]
[TR]
[TD="class: xl72"] 1[/TD]
[TD="class: xl76"]0.043[/TD]
[/TR]
[TR]
[TD="class: xl72"] 2[/TD]
[TD="class: xl76"]0.035[/TD]
[/TR]
[TR]
[TD="class: xl75"] 3[/TD]
[TD="class: xl74"]0.028[/TD]
[/TR]
</tbody>[/TABLE]

Table that is in the spreadsheet:

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl71, width: 64"][TABLE="width: 256"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 3"]Deductible Discount Factors
Table:[/TD]
[/TR]
[TR]
[TD]Deduct.
Amount[/TD]
[TD] 1[/TD]
[TD] 2[/TD]
[TD] 3[/TD]
[/TR]
[TR]
[TD] 1,000[/TD]
[TD]0.043[/TD]
[TD]0.035[/TD]
[TD]0.028[/TD]
[/TR]
[TR]
[TD] 2,000[/TD]
[TD]0.072[/TD]
[TD]0.059[/TD]
[TD]0.049[/TD]
[/TR]
[TR]
[TD] 3,000[/TD]
[TD]0.094[/TD]
[TD]0.079[/TD]
[TD]0.065[/TD]
[/TR]
[TR]
[TD] 4,000[/TD]
[TD]0.114[/TD]
[TD]0.095[/TD]
[TD]0.079[/TD]
[/TR]
[TR]
[TD] 5,000[/TD]
[TD]0.131[/TD]
[TD]0.109[/TD]
[TD]0.091[/TD]
[/TR]
[TR]
[TD] 10,000[/TD]
[TD]0.198[/TD]
[TD]0.165[/TD]
[TD]0.137[/TD]
[/TR]
[TR]
[TD] 15,000[/TD]
[TD]0.246[/TD]
[TD]0.206[/TD]
[TD]0.172[/TD]
[/TR]
[TR]
[TD] 20,000[/TD]
[TD]0.283[/TD]
[TD]0.238[/TD]
[TD]0.201[/TD]
[/TR]
[TR]
[TD] 25,000[/TD]
[TD]0.313[/TD]
[TD]0.265[/TD]
[TD]0.225[/TD]
[/TR]
[TR]
[TD] 50,000[/TD]
[TD]0.415[/TD]
[TD]0.361[/TD]
[TD]0.313[/TD]
[/TR]
[TR]
[TD] 75,000[/TD]
[TD]0.476[/TD]
[TD]0.422[/TD]
[TD]0.375[/TD]
[/TR]
[TR]
[TD] 100,000[/TD]
[TD]0.516[/TD]
[TD]0.467[/TD]
[TD]0.424[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl72, width: 64"][/TD]
[TD="class: xl72, width: 64"][/TD]
[TD="class: xl72, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

 

[L. Howard]

So the little box at the top is for 1,000 selected in the drop down?

And if 50,000 was selected the Ded's would be 0.415 - 0.361 - 0.313 ??

Howard

 

[L. Howard]

I think this will do it.

With Table & Ded in cells D1 & E1

Drop Down in cell G1 (1,000 to 100,000)

E2 =VLOOKUP(G1,$D$8:$E$19,2,0)
E3 =VLOOKUP(G1,$D$8:$F$19,3,0)
E4 =VLOOKUP(G1,$D$8:$G$19,4,0)

With the 1000 value of the table in cell D8 and the table follows to G19.

Howard

 

[Castillb]

So the little box at the top is for 1,000 selected in the drop down?

And if 50,000 was selected the Ded's would be 0.415 - 0.361 - 0.313 ??

Howard

Hi Howard,

I apologize for not being more clear in my question. Just to clarify, I want column E to update based on the deductible value that is selected in the drop-down and the table number (1,2 or 3) that is in column D. So for example, if 5,000 is selected from the drop-down and the table number in column D is 1, return value 0.131, if the table number in column D is 2 return value .109, etc, etc....I'd like to do the same for all deductible amounts.

 

[L. Howard]

I thought I was showing that with the VLOOKUP's and the drop down in G1.

Here is my example on a worksheet.

Take a look and let me know what needs to be done different.

https://www.dropbox.com/s/y5umer4f98bc4m3/Book5 Deductibles.xlsm?dl=0


Howard

 

[Castillb]

I thought I was showing that with the VLOOKUP's and the drop down in G1.

Here is my example on a worksheet.

Take a look and let me know what needs to be done different.

https://www.dropbox.com/s/y5umer4f98bc4m3/Book5 Deductibles.xlsm?dl=0


Howard

Howard,

In your workbook, the table numbers are in cells D2:D4. Is there a way for cells E2:E4 to auto-update if any of the cells in D2:D4 were to get manually updated by the user? That's what I am trying to accomplish. For example, if cell D2 were to get updated from a 1 to a 2, then cell E2 would become .109?

Thank you so much for your time and help. I really appreciate it.

 

[L. Howard]

Try the link in Post #5 again, I made some changes, heading in the right direction I hope.

Howard