Excel or VBA code to compare variable against column of figures & perform a function

[Charlie4]

Hi All

Take a look at the example table below.

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Row[/TD]
[TD]Age[/TD]
[TD]Age Multiple[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]17[/TD]
[TD]0.8[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20[/TD]
[TD]1.0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]25[/TD]
[TD]1.15[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]35[/TD]
[TD]1.20[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]50[/TD]
[TD]1.22[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]70[/TD]
[TD]1.23[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: outer_border, width: 200"]
<tbody>[TR]
[TD]Age[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Age x Age Multiple[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

If I have a variable "Age" on the sheet (populated from a UserForm) I need excel to look down Age column until it finds the right age range, multiply that age by the value in the Age Multiple Column and return that value.


For example if Age = 40, it should look down the Age column till it gets to row 4 (for ages between 35-50), multiply by the Age Multiple for that group (1.2) and return the result (48).


What is the correct VBA or excel code for this? A for/next loop?


(the actual table is a lot larger and the function more complicated than a simple multiplication)


Many thanks for any help!


Kind regards
Charlie

 

[Gerald Higgins]

Hi.

Let's assume your table is laid out so that the word "AGE" is in cell A1, the number 15 is in cell A2, the multiple 0.8 is in cell B2, and so on.
Let's assume your input number of 40 is contained in cell C1.

=C1*VLOOKUP(C1,A2:B7,2,TRUE)

 

[Charlie4]

Hi.

Let's assume your table is laid out so that the word "AGE" is in cell A1, the number 15 is in cell A2, the multiple 0.8 is in cell B2, and so on.
Let's assume your input number of 40 is contained in cell C1.

=C1*VLOOKUP(C1,A2:B7,2,TRUE)

Gerald - that's perfect. Exactly what I'm looking for.
Many thanks.
C.