Index Match variable value - greater than less than question

[karcpr]

Hi everyone,
I have been doing greater than less than in a HUGE nested AND function, however, I understand that there is a very simple way to do a index + match that will capture greater than (or equal) / less than (or equal). Can you help me to ID if this formula exists? See below for a sample example.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

cost 1	cost 2	cost 3	cost 4	cost 5	cost 6	cost 7		break 1	break 2	break 3	break 4	break 5	break 6	break 7		6 month demand
0.8	0.5	0.3	0.1	0.05	0.035	0.03		1,000	2,500	5,000	10,000	25,000	500,000	100,000		3,500
0.095	0.07	0.06	0.05	0.04	0.03	0.025		1,000	2,500	5,000	10,000	25,000	500,000	100,000		5,800

<tbody>
</tbody>

 

[ChrisM]

You haven't specified what you are trying to accomplish in your sample data....

 

[karcpr]

Sorry for the misdirection.

My goal is to find where the 6 month demand falls into the break column and as result come up with the cost that is associated with it. Break 1 is associated with cost 1, break 2 = cost 2 and so on.

In the old way of doing everything this is what I am trying to get (these would be nested into each other).

BREAK 1
IF(6 MONTH DEMAND < BREAK 2, COST 1,)

BREAK 2
IF(AND(6 MONTH DEMAND BREAK 2)),COST 2,)

BREAK 3
IF(AND(6 MONTH DEMAND BREAK 3)),COST 3,)

BREAK 4
IF(AND(6 MONTH DEMAND BREAK 4)),COST 4,)

BREAK 5
IF(AND(6 MONTH DEMAND BREAK 5)),COST 5,)

BREAK 6
IF(AND(6 MONTH DEMAND BREAK 6)),COST 6,)

BREAK 7
IF(6 MONTH DEMAND>BREAK 7,COST7,)

 

[ChrisM]

I have to run, so haven't tested this, but this should get you close:

=INDEX(A1:G1,,IFERROR(MATCH(O1,H1:N1,1),1))

where A1:G1 is cost, O1 is demand lookup, and H1:N1 is demand breaks.

 

[karcpr]

That is great. What function does the Iferror play? I know what iferror does, however, how does the iferror differentiate the part?

I have an addition to the question on this process but I will start start a new thread.

 

[ChrisM]

Match is used with the 1 flag here, which means it is in "find the smallest match" mode. However, it will error out if you enter a value smaller than your first demand break. Your first break is at 1,000, so if you try to look up 750 you will get an error. I assumed everything below 1,000 should return the same break as 1000-2499, so IFERROR(xxx,1) will return the value 1 in this case, and when you pass 1 to the INDEX function, it will return the first cost break.

If that logic is not correct, we'll either need to change the formula or have you add a zero demand cost break.

 

[karcpr]

Totally makes sense.

 

[karcpr]

Okay, here is a quick change in the thought process. This is how the pricing file usually looks when it is transmitted over to us.

Cost1	Cost2	Cost3	Cost4	Cost5	Cost6	Max Qty1	Max Qty2	Max Qty3	Max Qty4	Max Qty5	Max Qty6	6 MONTHS SUPPLY
3.59	2.49	1.82	1.72	1.68	1.63	499	999	2499	4999	9999	9999999	499
42.99	36.51	31.21	22.97	21.79	21.2	49	99	249	499	999	9999999	69
0.4553	0.4009	0.3602	0.333	0.2922	0.2786	9999	19999	49999	99999	249999	999999999	21,898
0.074	0.0714	0	0	0	0	49999	9999999	0	0	0	0	106,418
0.0999	0.0864	0	0	0	0	24999	9999999	0	0	0	0	26,584
4.74	3.74	3.21	2.85	2.37	0	999	4999	9999	24999	9999999	0	1,954
1.44	1.34	0	0	0	0	4999	9999999	0	0	0	0	2,462
1.81	1.69	1.57	0	0	0	999	4999	9999999	0	0	0	6,000
2.55	2.02	1.89	1.76	0	0	499	999	4999	9999999	0	0	2,391
0.0806	0.0697	0	0	0	0	24999	9999999	0	0	0	0	22,796
0.0815	0.075	0.0707	0	0	0	24999	49999	9999999	0	0	0	48,800
0.0839	0.0725	0	0	0	0	24999	9999999	0	0	0	0	137,500
0.1404	0.132	0	0	0	0	249999	9999999	0	0	0	0	29,805
0.079	0.0728	0.0686	0	0	0	24999	49999	9999999	0	0	0	34,861
0.0627	0	0	0	0	0	9999999	0	0	0	0	0	1,259,500

<colgroup><col span="6"><col span="5"><col><col></colgroup><tbody>
</tbody>

This is the format that everything currently is in. My issue is that I can manipulate the data to the min qtys and take out the 0's but when we are working on monsterous files, it makes it extremely time consuming. Do you know of a way that we can use the same formula and instead of using the pricing prior, you use the pricing max? The 6 month supply would still be what you are trying to get to.

To add complexity to the matter, would it be out of the realm of crazy to add a tolerance to the 6 month supply to state "if the 6 month supply is within x% of the next break, go to the next break. I theoretically could make a new column that that has an if function that states if 20% more is not the same break as the 6 month supply, to do the same formula. Does that make sense?