INDEX MATCH - Selecting lowest value in a range of values

This will probably seem pretty simple but it's been kicking my behind all day.

I have a table formatted like this:

MODEL VOLUME COST
A11111 2000 .0055
A11111 3000 .0045
B22222 2500 .0060
B22222 5000 .0050

My input cells are: MODEL and VOLUME
The output should be the cost.

Using A11111 for MODEL from a drop down menu and entering a variable number into the VOLUME Field:

if the volume is less 3000 the cost should be .0055
if the volume is greater than 3000 the cost should be .0045

So, if I use A11111 for MODEL and 2500 for VOLUME the COST should be: .0055

Using this formula I can get an exact match:

Please Login or Register  to view this content.

I still need to be able to get the "in betweens". I cannot sort the table. Thanks for any help in advance!

Rob

Thanks! I've been lurking around here for a good while. Very helpful posts!!

You are correct. There are hundreds of models and each one has a different volume/cost structure.

I'll play round with IF's and see what I can come up with.

ok. I will construct a workbook and post it. I have to run an errand but will revisit when I return. Thanks!!

TESTBOOK.xlsx

Here is a sample workbook.

This formula gets me so close... I've been working on ways to get the true/false values to work out.

Please Login or Register  to view this content.

I played with this and found a solution that works. I have attached a workbook with the solution integrated. Using INDEX/MATCH and inserting a value of zero at the lower end I was able to make the formula work. It's workable within the real table and as I am changing it I will work with it's structure to make it sortable. Kind of like killing two birds with one stone.

Thanks!

Rob

TESTBOOK.xlsx

This is the code that works:

Please Login or Register  to view this content.