Fetching a value from a range depending on input

[Semir Teskeredzic]

Hello,

I have encountered an issue with a formula to calculate the price in my social entrepreneurship. Here is the simplified version. I enter the number of units in C4. Among other expenses, I have an expense in C7 that rises depending on the number in C4. If the number of units (C4) is in the range from 1 to 25 the expense is 1.5 if it is from 26 to 50 the expense is 3.0 and that goes up by 1.5 for each range. I want a formula that takes C4 checks what the expense is and writes it to C7.

I have tried with generating a table with rows like 1   25     1.5 , and using =IF(AND(C4>=A20,C4<=B20),D20,IF(AND(C4>=A21,C4<=B21),D21,IF ... etc and it works but I have to manually put into IF all the rows which would not be an issue although it would be horrible to write that much but it doesn't let me to do that due to the length of the formula.

I have tried an approach with index and match but it returns #N/A 

=INDEX($D$20:$D$99, MATCH(C4, A20:B99,0))

I have also tried using match type 1,0 or -1 but it returns the same error.

Again I need a way to test C4 against the given ranges and if it is true then return the given number that represents expenses. Perhaps the solution might be to get rid of the whole ranges table and to somehow tell excel to test the number where it belongs but I can't really comprehend on how to do that.

This is the sheet in question. The numbers in the A20:B99 table goes up to 2000 units, and that is the limit.

Thank you for any input, I would really appreciate it. Cheers

[#2]

Strange, INDEX/MATCH shall work. Are you sure in A column you have numbers, not text?

[#3]

I have reformated all fields involved to numbers but it stayed the same.

[#4 · Accepted solution]

Okay, it looks like i found. In MATCH it shall be A20:A99, not A20:B99. It works on one dimension array, not on matrix.

[#5]

It works. Thank you so much, it takes great skill to see the details. Much appreciated, thank you again. All the best.