Lookup

[neeraj]

I have to create a formula in say cell B25 to calculate a value called say
'multiplier' based on ratio (or percentage) of attainment with respect to
goals. I have a cell say B23 which contains the value of this percentage in
one cell of my worksheet and based on this value, I have to find a
multiplier. I have the following table for finding the multiplier. eg. if the
ratio (or percentage was 115% (value in B23) then this formula should be able
to look up the multiplier in the follwing table; based on this table, it
would fall in the 3rd row range and should result 1.1

From To Multiplier
0% 49% 0
50% 109% 1
110% 119% 1.1
120% 129% 1.2
130% 139% 1.3
140% 149% 1.4
150% 169% 1.5
170% 199% 1.6
200% 239% 1.7
240% + 2
How can I do this

 

[Richard Buttrey]

I have to create a formula in say cell B25 to calculate a value called say
'multiplier' based on ratio (or percentage) of attainment with respect to
goals. I have a cell say B23 which contains the value of this percentage in
one cell of my worksheet and based on this value, I have to find a
multiplier. I have the following table for finding the multiplier. eg. if the
ratio (or percentage was 115% (value in B23) then this formula should be able
to look up the multiplier in the follwing table; based on this table, it
would fall in the 3rd row range and should result 1.1

From To Multiplier
0% 49% 0
50% 109% 1
110% 119% 1.1
120% 129% 1.2
130% 139% 1.3
140% 149% 1.4
150% 169% 1.5
170% 199% 1.6
200% 239% 1.7
240% + 2
How can I do this

The table below is functionally equivalent - format the first column
as a %


0.50 1
1.10 1.1
1.20 1.2
1.30 1.3
1.40 1.4
1.50 1.5
1.70 1.6
2.00 1.7
2.40 2

Give the table a range name - say "MyTable"

Now in B25 enter the formula

=vlookup(B23,MyTable,2)


Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Gord Dibben]

One method without a table.

=LOOKUP(B23,{0,50,110,120,130,140,150,170,200,240},{0,1,1.1,1.2,1.3,1.4,1.5,1.6,1.7,2})


Gord Dibben Excel MVP

 

[neeraj]

Thanks, it worked except the first row, if the value to be looked up is less
than 0.5, it gives a #N/A error (this is as expected because if lookup_value
is smaller than the smallest value in the first column of table_array,
VLOOKUP returns the #N/A error ). I had to add another row to your table:
0.0% 0
0.50 1
1.10 1.1
......