looking things up in a range part 1

[Lawrence Dodge]

Hi all --
I have a situation where in Cell A1 there is a set value. When I enter a value into Cell A2, I need it to lookup a chart of values determined by a range for instance
0 - 50,000 gives a multiplier (acting on cell A1) of 0.80,
50,001 - 100,000 gives a multiplier of 0.70
100,001 - 150,000 gives a multiplier of 0.60
etc.

The ranges might change in the future so I have them on a separate sheet (Quality Schedule), but if there is a better way to construct this that is fine. Non VBA would be preferred.

Thanks

 

[NARAYANK991]

Hi Lawrence ,

Is your table likely to have many more entries , or will the number of multipliers be just 3 or 4 ?

Using a range in a lookup is difficult ; it is better to use a lookup table of the lower limits such as 0 , 50,001 , 100,001 ,... or the upper limits such as 50,000 , 100,000 , 150,000 ,...

Narayan

 

[Lawrence Dodge]

Hi Narayan --
Currently this is what the table looks like (sorry the columns are crunched):
050,0000.80
50,001100,0000.70
100,001150,0000.60
150,001200,0000.50
200,001250,0000.40
250,001300,0000.30
300,001350,0000.20
350,001500,0000.00
500,001600,000-0.20
600,001750,000-0.40
750,001850,000-0.60
851,00110,000,000-0.80

 

[Lawrence Dodge]

the uploaded file has better column separation (the colors are just to highlight different columns -- they can be deleted)

 

[NARAYANK991]

Hi Lawrence ,

See your file ; I have used a named range Multipliers for your lookup table.

Narayan