CODEWITHSUNDEEP
excelexcel-formula
John
John

Reputation: 141

Return a value from a table if a number is between two values

I'm looking to automate an price list spreadsheet price. Currently we use a pricing table that consists something like this:

£2.10 - £2.90 = £9.99

£2.91 - £3.70 = £10.40

I don't know the formula exactly so is there a way to import this table so when I enter a number it returns the correct value in another column.

I know I can use loads of if statements but there must be an easier way?

Upvotes: 0

Views: 3380

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33145

First, import your pricing table or paste it into Excel. You'll probably end up with each line in its own cell. You need to use Data > Text to Columns to split the data into three columns. First split on - then again on = to get three columns like this

2.10  2.90   9.99
2.91  3.70  10.40

To covert a value, use VLOOKUP and make the last argument TRUE. This requires that your new pricing table be sorted, but it probably already is. With TRUE as the last argument, VLOOKUP will find the largest value that's less than or equal to the lookup value.

Here's what the formula looks like with your value in E5 and the pricing table in C1:C2:

=VLOOKUP(E5,$A$1:$C$2,3,TRUE)

Here are some results:

2.12    9.99
2.9     9.99
2.91    10.4
3.1     10.4
3.8     10.4

When it looks up 2.12, it goes down column A until it finds a value higher (2.91 in this case) then returns the row just before it.

Notice how it only uses column A. Column B is there just for show, but it creates a problem when you enter a price higher than 3.7 - it still returns 10.4. You could fix this with a SUMPRODUCT formula to check both upper and lower bounds, but it's easier to just add a last row to the table

3.8   9999.99   NA

So that it won't return a bad value. Note also that anything below 2.1 will return an error (NA) because it can't find a row before the row with the highest value.

Upvotes: 1

Related Questions