Help index match match with roundown

[Unexcellent]

I need to index match match but the data i am trying to find is in a table like below. The cost of the units needed is always rounded down so in the Cost column for 2478 units i want the cost of 2000 units against code 4/1 therefore £773, can anyone help??

CODE	500	1000	1500	1700	2000	2500	3000		Code	Units Needed	Cost
1/1	£601	£613	£631	£639	£650	£670	£689		4/1	2478
4/1	£724	£736	£754	£762	£773	£793	£812		4/4hs	1145
4/2	£793	£805	£823	£831	£842	£862	£882		4/4+	2435
4/4	£739	£751	£768	£774	£783	£800	£816		4/4hs	1367
4/4+	£1,181	£1,193	£1,210	£1,217	£1,228	£1,248	£1,266		2/5	2223
4/4hs	£870	£881	£898	£906	£917	£936	£955		4/4hs	1688
2/3+	£1,059	£1,070	£1,087	£1,095	£1,105	£1,125	£1,144		4/4hs	3500
2/3hs	£747	£758	£776	£783	£794	£813	£832		4/4hs	1234
3/1	£903	£914	£929	£935	£944	£961	£976		4/4hs	2280
2/5	£861	£873	£891	£898	£909	£930	£949		4/4hs	1447
4/5	£943	£955	£973	£980	£991	£1,012	£1,031		4/2	3003
5/5	£1,015	£1,027	£1,045	£1,052	£1,063	£1,084	£1,103		4/4hs	1798

<colgroup><col width="64" style="width: 48pt;" span="9"> <col width="115" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4205;"> <col width="176" style="width: 132pt; mso-width-source: userset; mso-width-alt: 6436;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

 

[baitmaster]

welcome to the board

With the first table located at A1:H13, and hence your lookup table located at J1:L13...

Cell L2, then copied downwards:
=INDEX($B$2:$H$13,MATCH(J2,$A$2:$A$13,0),MATCH(K2,$B$1:$H$1,1))

The trick is to do with the third argument of the second MATCH formula. 0 requires exact match, but you don't want this you want nearest match that's lower or equal, hence +1

 

[Unexcellent]

Thank you that works great, the only problem is that i have some unites needed values that are lower than 500 and as they round down they don't show a price, is there anything i can add to the formula so that for a number equal to or smaller than 499 is rounded up to 500? Thanks again fast reply and informative answer.

 

[Unexcellent]

So if i had a table like this: How would i find the price for 4/4hs and 60 units, it should show £870 but its showing 0 as its rounded down

CODE	500	1000	1500	1700	2000	2500	3000		Code	Units Needed	Cost
01-Jan	£601	£613	£631	£639	£650	£670	£689		4/1	2478
04-Jan	£724	£736	£754	£762	£773	£793	£812		4/4hs	1145
04-Feb	£793	£805	£823	£831	£842	£862	£882		4/4+	2435
04-Apr	£739	£751	£768	£774	£783	£800	£816		4/4hs	1367
4/4+	£1,181	£1,193	£1,210	£1,217	£1,228	£1,248	£1,266		2/5	2223
4/4hs	£870	£881	£898	£906	£917	£936	£955		4/4hs	60
2/3+	£1,059	£1,070	£1,087	£1,095	£1,105	£1,125	£1,144		4/4hs	3500
2/3hs	£747	£758	£776	£783	£794	£813	£832		4/4hs	450
03-Jan	£903	£914	£929	£935	£944	£961	£976		4/4hs	2280
02-May	£861	£873	£891	£898	£909	£930	£949		4/4hs	1447
04-May	£943	£955	£973	£980	£991	£1,012	£1,031		4/2	3003
05-May	£1,015	£1,027	£1,045	£1,052	£1,063	£1,084	£1,103		4/4hs	1798

<tbody>
</tbody><colgroup><col><col span="7"><col><col><col><col></colgroup>

 

[FormR]

is there anything i can add to the formula so that for a number equal to or smaller than 499 is rounded up to 500?

Hi, welcome to the forum!

You could try this small amendment to baitmasters suggestion.

=INDEX($B$2:$H$13,MATCH(J2,$A$2:$A$13,0),MATCH(MAX($B$1,K2),$B$1:$H$1,1))

 

[Andrew_UK]

Or just change the minimum order quantity from 500 to zero?

My understanding from your initial question would be that 500-999 needs to go off column B, 1000-1499 needs to go off column C, 1500-1699 needs to go off column C etc...

But if you're saying 0-499 needs to go off the same as column B then 0-499 is following the same pattern as 500-999... so really it's 0-999?

 

[Unexcellent]

Andrew_UK that has done it! I should of been able to apply that logic myself it seems so obvious now that you've pointed it out. Thanks to all!

 

[Unexcellent]

if i have calculated the stock needed for 1 week, 2 weeks, 3 weeks etc and the total price for each week below, how do i insert a box that tells me what the cheapest option is i.e. what the cheapest price will be per week if i buy and how many weeks i need to order for to get that price? I cant seem to find any help for this online

Units Needed for 8 Weeks	Cost	Units Needed for 9 Weeks	Cost	Units Needed for 10 Weeks	Cost	Units Needed for 11 Weeks	Cost	Units Needed for 12 Weeks	Cost
27196	1741	31083.75	1958	34489.5	1958	37895.25	1958	41301	1958
18113	1490	20638.5	1661	22888	1661	25137.5	1872	27387	1872
6554	1074	7385.75	1114	8217.5	1153	9049.25	1153	9881	1153
1546	898	1746.25	906	1946.5	906	2146.75	917	2347	917
558	870	722.25	870	886.5	870	1050.75	881	1215	881
Storage Cost	923.0769	Storage Cost	1038.462	Storage Cost	1153.846	Storage Cost	1269.231	Storage Cost	1384.615
Total Cost (Stock + Storage)	9691.077	Total Cost (Stock + Storage)	12161.46	Total Cost (Stock + Storage)	14128.85	Total Cost (Stock + Storage)	14495.23	Total Cost (Stock + Storage)	15047.62
Total Cost P/Week	1211.385	Total Cost P/Week	1351.274	Total Cost P/Week	1412.885	Total Cost P/Week	1317.748	Total Cost P/Week	1253.968
Cheapest Option:
Cost P/Week:

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

 

[FormR]

Hi, just a couple of things that might help you with any future postings:

1. You should really start a new thread for new questions; by doing so you will get more potential helpers looking at your question as many will skip over posts that already have multiple replies.
2. When you present sample data always try to include what your expected results are in relation to that sample data.

Is this the result you are looking for:

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1	Units Needed for 8 Weeks	Cost	Units Needed for 9 Weeks	Cost	Units Needed for 10 Weeks	Cost	Units Needed for 11 Weeks	Cost	Units Needed for 12 Weeks	Cost
2	27196	1741	31083.75	1958	34489.5	1958	37895.25	1958	41301	1958
3	18113	1490	20638.5	1661	22888	1661	25137.5	1872	27387	1872
4	6554	1074	7385.75	1114	8217.5	1153	9049.25	1153	9881	1153
5	1546	898	1746.25	906	1946.5	906	2146.75	917	2347	917
6	558	870	722.25	870	886.5	870	1050.75	881	1215	881
7	Storage Cost	923.0769	Storage Cost	1038.462	Storage Cost	1153.846	Storage Cost	1269.231	Storage Cost	1384.615
8	Total Cost (Stock + Storage)	9691.077	Total Cost (Stock + Storage)	12161.46	Total Cost (Stock + Storage)	14128.85	Total Cost (Stock + Storage)	14495.23	Total Cost (Stock + Storage)	15047.62
9	Total Cost P/Week	1211.385	Total Cost P/Week	1351.274	Total Cost P/Week	1412.885	Total Cost P/Week	1317.748	Total Cost P/Week	1253.968
10
11	Cheapest Option:	Units Needed for 8 Weeks
12	Cost P/Week:	1211.385
Sheet1

Cell Formulas
Range	Formula
B11	=INDEX(A1:J1,MATCH(B12,A9:J9,0)-1)
B12	=MIN(B9:J9)

 

[Unexcellent]

Thank you MrExcel MVP, I assumed that because i already had the attention of a few excel pros that i should stay on the same post but i will make a new one for each new question in the future and yes that was what i was looking for! Thanks again!