INDEX MATCH - Selecting lowest value in a range of values

[indyrob]

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

[FDibbins]

Hi and welcome to the forum

This seems like a pretty straight-forward IF() (or maybe nested IF) formula?

based on this...

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

something like...
=if(A1<3000,.0055,.0045)

You didnt mention anything about how the A and B models play into this, so I haver a feeling this is not quite this simple, right?

[martindwilson]

I cannot sort the table

and why not?

[indyrob]

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.

[FDibbins]

If you have "hundreds of models", then IF() is not thw way to go.

Can you upload a SMALL sample workbook, showing what you have and what you want?

[martindwilson]

the thing is the construct $F$7&$G$7 makes text string so can never be used in a range argument in match against a number value

[FDibbins]

Im thinking that a table with models in column A and Volumes across the top in row 1, will let you use an index/match/match or vlookup/match function

[indyrob]

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

[indyrob]

TESTBOOK.xlsx

Here is a sample workbook.

[indyrob]

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.

[indyrob]

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.

[martindwilson]

INDEX(COST, MATCH(E18&F18, (MODEL&VOLUME), 1)) works as you have found because fortunately the addition of a zero doesnt affect the fact that the when concatenated they are sorted ascending it might not work if you add models in a different alphabetical order