#### rawnaah

##### New Member

 SECONDARY TRANSPORT RATE: 251 TO 400 KM & >400 \$ 24 PER MT PER KM SECONDARY TRANSPORT RATE: 101 TO 250 KM \$ 25 PER MT PER KM SECONDARY TRANSPORT RATE: 31 TO 100 KM \$ 25 PER MT PER KM SECONDARY TRANSPORT RATE: 11 TO 30 KM \$ 25 PER MT PER KM SECONDARY TRANSPORT RATE: <=10 \$ 600 PER MT

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Your question is not exactly clear..

Can you explain in words what you're trying to do (along with the sample you posted)

Best guess is something like a lookup
=LOOKUP(A1,{0,11,251},{600,25,24})

I'm having trouble following your table, and not sure the logic you want to use - are you using multiple rates for each thing - as in, 0-10 has \$600, then add an additional \$25 for KMs 11 to 30, then an additional \$25 for KMs 31 to 100?

That's the only way I can make sense of the lowest tier being the higher \$ amount... It also makes the solution vastly different than a simple IF statement.

a vlookup would work like this:

Excel 2010
ABC
1<=10 \$0600 PER MT PER KM
211 TO 30 KM \$1125 PER MT PER KM1
331 TO 100 KM \$3125 PER MT PER KM2
4101 TO 250 KM \$10125 PER MT PER KM3
5251 TO 400 KM & >400 \$25124 PER MT PER KM
6
7
830024 PER MT PER KM
9150
1075
1120
128
Sheet1
Cell Formulas
RangeFormula
B8=VLOOKUP(A8,B1:C5,2)

Thanks for your responses,i'll see what i can to best clearly explain..

Actually,i wanted to use one IF function to provide an output on the following arguments. For 10km or less=\$600,between 11km to 30km=\$25,between 31km to 100km=\$25,between 101km to 250km=\$25 and between 251km to 400km & more= 24.

I am not sure i've made enough clarity there.Sorry! thanks again

What value are you trying to compare with these ranges?
A value in a Cell? Which Cell?
Does that cell contain just a number, or is it a text string like 101km?

These 3 statements are redundant
between 11km to 30km=\$25,between 31km to 100km=\$25,between 101km to 250km=\$25
is the same as saying
between 11km to 250km=\$25

If you put a number (JUST a number like 50 instead of 50km) in a cell, say A1
Then this formula does what you're looking for

=LOOKUP(A1,{0,11,251},{600,25,24})

Last edited:
Jonmo1 i see your point about redundancy in those 3 statements,but that is how the data is.As for the value in the cell that am trying to compare? yeah it does contain km.i'll see if i can post the data as it is in my spreadsheet.

Thanks tex but it is only applying up to cell B11(in your example),the remaining cells (from B12 up to down containing values are showing errors e.g# N/A)..

Thanks tex but it is only applying up to cell B11(in your example),the remaining cells (from B12 up to down containing values are showing errors e.g# N/A)..

Lock the range B1:C5 as follows:

=VLOOKUP(A8,\$B\$1:\$C\$5,2)

Lock the range B1:C5 as follows:

=VLOOKUP(A8,\$B\$1:\$C\$5,2)

Ok now a cell in column A with value 254 gave an output of 600 instead of 24(as per 251 to 400km> 400) range,thanks.

Replies
9
Views
231
Replies
2
Views
221
Replies
11
Views
270
Replies
3
Views
230
Replies
5
Views
125

1,196,516
Messages
6,015,679
Members
441,915
Latest member
sm Hussaini

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back