Range Value

[Smithsc]

Is there a quick formula to do the following:
I've got the table below

A B C
1 Minimum Maximum Range
2 0 49,999.99 < £50k
3 50,000.00 249,999.99 < £250k
4 250,000.00 649,999.99 < £650k
5 650,000.00 1,999,999.99 < £2m
6 etc

and a list of values

Value
18,456.25
23,698.54
1,222,369.56

I want to be able to calculate which range the values fall into. I don't want hard code the ranges into the formula incase they change.

Thanks

Stuart.

 

[SkipVought]

hi,

Use Match and Index and modify your table to use the LOWER value in the range
[tt]
A B
1 Minimum Range Value
2 0 £50k
3 50,000.00 £250k
4 250,000.00 £650k
5 650,000.00 £2m
.....
[/tt]
[tt]
=INDEX(Minimum),MATCH(YourValueRef,Range_Value,1),1)
[tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}