Excel help - lookup between 2 values in table

[Mark M]

I have a sort of price list in Excel which has bands eg

30,000 £1000
50,000 £2000
75,000 £3000
100,000 £4000
150,000 £5000
250,000 £6000
350,000 £7000
550,000 £8000

Each band has a different price associated with it and I want to lookup the price depending on where a value falls within the bands. For example if I entered 120,000 into a cell I want the formula to return £5,000 or 275,000 would give you £7,000

Is there a formula that can do this or do i need to set out my data in a different way?

Thanks

 

[PMKeates]

Place your data table in A1, but modify it slightly:

0 £1,000
30,000 £2,000
50,000 £3,000
75,000 £4,000
100,000 £5,000
150,000 £6,000
250,000 £7,000
350,000 £8,000
550,000

(550,000 is effectively doing nothing here, so you might want to add a value)

Then =VLOOKUP(valuetosearch,A1:B8,2,TRUE)

If you absolutely can't modify the data table, use the formula:

=INDEX(B1:B8,MATCH(valuetosearch,A1:A8,1)+1)

Note that in both these cases, if you exactly match the value, you get the next rate. If you need it to match say 100,000 to £4000 and not £5000, do =INDEX(B1:B8,MATCH(valuetosearch-1,A1:A8,1)+1).