Search for a value with two criterias

pasjauo

New Member
Hello all!

I have a project with a client and I would like a slick solution instead of what I usually come up with, which is bulky and slow.

I am calculating a shipping price, where i have the zone and weight of the container. This held together with destination country should give me the final price.

Every destination country has a zone in a zone map, And i have made a VLOOKUP to a cell where i have the zone number. So I always have the zone number when they choose a destination country in the drop down menu I have created.

My problem is now that i need to search a matrix on both the zone and the weight (which is also inputted).

For the sake of the example my Zone number is in cell A1 and weight is input in A2. The matrix for the price is on Sheet2.

How do i do that?
 

jorismoerings

Well-known Member
Hi,

Just as an example on how to approach:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">400</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Zone / weight</td><td style="text-align: right;;">100</td><td style="text-align: right;;">200</td><td style="text-align: right;;">300</td><td style="text-align: right;;">400</td><td style="text-align: right;;">500</td><td style="text-align: right;;">600</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">zone</td><td style=";">weight</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">0,982241</td><td style="text-align: right;;">0,152395</td><td style="text-align: right;;">0,923771</td><td style="text-align: right;;">0,465143</td><td style="text-align: right;;">0,561483</td><td style="text-align: right;;">0,527555</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">0,216614</td><td style="text-align: right;;">0,955084</td><td style="text-align: right;;">0,924827</td><td style="text-align: right;;">0,020197</td><td style="text-align: right;;">0,402408</td><td style="text-align: right;;">0,694049</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;">0,763983</td><td style="text-align: right;;">0,487043</td><td style="text-align: right;;">0,628271</td><td style="text-align: right;;">0,114754</td><td style="text-align: right;;">0,783694</td><td style="text-align: right;;">0,368298</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">0,114754</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">0,854205</td><td style="text-align: right;;">0,882279</td><td style="text-align: right;;">0,986413</td><td style="text-align: right;;">0,739743</td><td style="text-align: right;;">0,892222</td><td style="text-align: right;;">0,08628</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;">0,55451</td><td style="text-align: right;;">0,235303</td><td style="text-align: right;;">0,087284</td><td style="text-align: right;;">0,172758</td><td style="text-align: right;;">0,865933</td><td style="text-align: right;;">0,178537</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">6</td><td style="text-align: right;;">0,673269</td><td style="text-align: right;;">0,267535</td><td style="text-align: right;;">0,614898</td><td style="text-align: right;;">0,173497</td><td style="text-align: right;;">0,953625</td><td style="text-align: right;;">0,351748</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">7</td><td style="text-align: right;;">0,057207</td><td style="text-align: right;;">0,603249</td><td style="text-align: right;;">0,588881</td><td style="text-align: right;;">0,221016</td><td style="text-align: right;;">0,560819</td><td style="text-align: right;;">0,031514</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">8</td><td style="text-align: right;;">0,972826</td><td style="text-align: right;;">0,536599</td><td style="text-align: right;;">0,525604</td><td style="text-align: right;;">0,82434</td><td style="text-align: right;;">0,77269</td><td style="text-align: right;;">0,610188</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">9</td><td style="text-align: right;;">0,672433</td><td style="text-align: right;;">0,208521</td><td style="text-align: right;;">0,151488</td><td style="text-align: right;;">0,70969</td><td style="text-align: right;;">0,078463</td><td style="text-align: right;;">0,839913</td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B5</th><td style="text-align:left">=INDEX(<font color="Blue">F2:K10,MATCH(<font color="Red">A1,E2:E10,0</font>),MATCH(<font color="Red">B1,F1:K1,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

pasjauo

New Member
Hi,

Just as an example on how to approach:

ABCDEFGHIJK
13400Zone / weight100200300400500600
2zoneweight10,9822410,1523950,9237710,4651430,5614830,527555
320,2166140,9550840,9248270,0201970,4024080,694049
430,7639830,4870430,6282710,1147540,7836940,368298
50,11475440,8542050,8822790,9864130,7397430,8922220,08628
650,554510,2353030,0872840,1727580,8659330,178537
760,6732690,2675350,6148980,1734970,9536250,351748
870,0572070,6032490,5888810,2210160,5608190,031514
980,9728260,5365990,5256040,824340,772690,610188
1090,6724330,2085210,1514880,709690,0784630,839913

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3

Worksheet Formulas
CellFormula
B5=INDEX(F2:K10,MATCH(A1,E2:E10,0),MATCH(B1,F1:K1,0))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
That works PERFECTLY - thank you!

I have found out the weight-factor is a bit more complicated than just min-max unfortunately.

The weight goes from for instance 100-1000, and then different prices per 100 until 5000 and other price until 10000. In this case I tried to put in an IF formula, so if the weight goes over the limit it will show the price for 1000 and then i can with another formula calculate the price for the "rest" of the weight if you understand.

Code:
=INDEX([COLOR=Blue]F2:K10,MATCH([COLOR=Red]A1,E2:E10,0[/COLOR]),MATCH([COLOR=Red]IF(B1>1000,1000,B1),F1:K1,0[/COLOR])[/COLOR])
This fails with a reference error though. But if it just can show me the max price within the matrix if it goes over weight, then i can formulate another cell to calculate the rest.
 

jorismoerings

Well-known Member
hi,

The MATCH function in itself is already a check, so can't hold the IF part.
You could add the 1000 column to the table and have it return the lowest number in the table.
like this:
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1100</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Zone / weight</td><td style="text-align: right;;">100</td><td style="text-align: right;;">200</td><td style="text-align: right;;">300</td><td style="text-align: right;;">400</td><td style="text-align: right;;">500</td><td style="text-align: right;;">600</td><td style="text-align: right;;">1000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">zone</td><td style=";">weight</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">0,982241</td><td style="text-align: right;;">0,152395</td><td style="text-align: right;;">0,923771</td><td style="text-align: right;;">0,465143</td><td style="text-align: right;;">0,561483</td><td style="text-align: right;;">0,527555</td><td style="text-align: right;;">0,083482</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">0,216614</td><td style="text-align: right;;">0,955084</td><td style="text-align: right;;">0,924827</td><td style="text-align: right;;">0,020197</td><td style="text-align: right;;">0,402408</td><td style="text-align: right;;">0,694049</td><td style="text-align: right;;">0,378216</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;">0,763983</td><td style="text-align: right;;">0,487043</td><td style="text-align: right;;">0,628271</td><td style="text-align: right;;">0,114754</td><td style="text-align: right;;">0,783694</td><td style="text-align: right;;">0,368298</td><td style="text-align: right;;">0,669498</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">0,669498</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">0,854205</td><td style="text-align: right;;">0,882279</td><td style="text-align: right;;">0,986413</td><td style="text-align: right;;">0,739743</td><td style="text-align: right;;">0,892222</td><td style="text-align: right;;">0,08628</td><td style="text-align: right;;">0,57495</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;">0,55451</td><td style="text-align: right;;">0,235303</td><td style="text-align: right;;">0,087284</td><td style="text-align: right;;">0,172758</td><td style="text-align: right;;">0,865933</td><td style="text-align: right;;">0,178537</td><td style="text-align: right;;">0,53103</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">6</td><td style="text-align: right;;">0,673269</td><td style="text-align: right;;">0,267535</td><td style="text-align: right;;">0,614898</td><td style="text-align: right;;">0,173497</td><td style="text-align: right;;">0,953625</td><td style="text-align: right;;">0,351748</td><td style="text-align: right;;">0,923199</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">7</td><td style="text-align: right;;">0,057207</td><td style="text-align: right;;">0,603249</td><td style="text-align: right;;">0,588881</td><td style="text-align: right;;">0,221016</td><td style="text-align: right;;">0,560819</td><td style="text-align: right;;">0,031514</td><td style="text-align: right;;">0,98567</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">8</td><td style="text-align: right;;">0,972826</td><td style="text-align: right;;">0,536599</td><td style="text-align: right;;">0,525604</td><td style="text-align: right;;">0,82434</td><td style="text-align: right;;">0,77269</td><td style="text-align: right;;">0,610188</td><td style="text-align: right;;">0,021833</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">9</td><td style="text-align: right;;">0,672433</td><td style="text-align: right;;">0,208521</td><td style="text-align: right;;">0,151488</td><td style="text-align: right;;">0,70969</td><td style="text-align: right;;">0,078463</td><td style="text-align: right;;">0,839913</td><td style="text-align: right;;">0,614107</td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B5</th><td style="text-align:left">=INDEX(<font color="Blue">F2:L10,MATCH(<font color="Red">A1,E2:E10,0</font>),MATCH(<font color="Red">B1,F1:L1,1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

This way the weight in F1:L1 is the minimum weight so 450 will result in the number for 400 and 1050 will result in 1000.
Does this help?
If not please provide a sample of your tables and create a manual mockup of what the expected outcome of the formula should be.
 

pasjauo

New Member
Thank you very much for your reply.

I can provide a sample here (if i can manage to make it look good):

Weight/zone123456789
1100100100100100100100100100
2200200200200200200200200200
3300300300300300300300300300
4400400400400400400400400400
5500500500500500500500500500
6600600600600600600600600600
7700700700700700700700700700
8800800800800800800800800800
9900900900900900900900900900
10100010001000100010001000100010001000
Break / 1505050505050505050
Stop / 50300030003000300030003000300030003000
Break / 1454545454545454545
Stop / 200120001200012000120001200012000120001200012000

<tbody>
</tbody>


The break / 1 in this case means that every unit costs 50 from 10-50 because the stop / 50 means ultimately that 50 units costs 3000. And again the second break / 1 means that every unit from 50 cost 45 up to 200 units. Hopefully it makes sense to you.

I need to have some sort of calculation so i can type in any unit and it calculates a price (if below 10 units it will just find the price in the matrix, but if it above maximum matrix unit (in this case 10) then it needs to calculate the price. There is no need for an elegant solution, as I have a data sheet which is not visible. So it is easy to make steps in order to make the formulas simple but in 2-4 steps instead of one long formula, which no one understands.

My own take on it would be to make criterias based on the weight. So a 0/1 would determine if the calculation goes on to the next step (Is weight above 50 units? for instance) and then would calcuate based on the criteria steps. If there is a more elegant and viable solution I'm all ears.

<tbody>
</tbody>
 

jorismoerings

Well-known Member
Hi,

Something like this:
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">86</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="color: #333333;;">Weight/zone</td><td style="text-align: right;color: #333333;;">1</td><td style="text-align: right;color: #333333;;">2</td><td style="text-align: right;color: #333333;;">3</td><td style="text-align: right;color: #333333;;">4</td><td style="text-align: right;color: #333333;;">5</td><td style="text-align: right;color: #333333;;">6</td><td style="text-align: right;color: #333333;;">7</td><td style="text-align: right;color: #333333;;">8</td><td style="text-align: right;color: #333333;;">9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">zone</td><td style=";">weight</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;;">1</td><td style="text-align: right;color: #333333;;">100</td><td style="text-align: right;color: #333333;;">100</td><td style="text-align: right;color: #333333;;">100</td><td style="text-align: right;color: #333333;;">100</td><td style="text-align: right;color: #333333;;">100</td><td style="text-align: right;color: #333333;;">100</td><td style="text-align: right;color: #333333;;">100</td><td style="text-align: right;color: #333333;;">100</td><td style="text-align: right;color: #333333;;">100</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;;">2</td><td style="text-align: right;color: #333333;;">200</td><td style="text-align: right;color: #333333;;">200</td><td style="text-align: right;color: #333333;;">200</td><td style="text-align: right;color: #333333;;">200</td><td style="text-align: right;color: #333333;;">200</td><td style="text-align: right;color: #333333;;">200</td><td style="text-align: right;color: #333333;;">200</td><td style="text-align: right;color: #333333;;">200</td><td style="text-align: right;color: #333333;;">200</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;;">3</td><td style="text-align: right;color: #333333;;">300</td><td style="text-align: right;color: #333333;;">300</td><td style="text-align: right;color: #333333;;">300</td><td style="text-align: right;color: #333333;;">300</td><td style="text-align: right;color: #333333;;">300</td><td style="text-align: right;color: #333333;;">300</td><td style="text-align: right;color: #333333;;">300</td><td style="text-align: right;color: #333333;;">300</td><td style="text-align: right;color: #333333;;">300</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;;">3870</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;;">4</td><td style="text-align: right;color: #333333;;">400</td><td style="text-align: right;color: #333333;;">400</td><td style="text-align: right;color: #333333;;">400</td><td style="text-align: right;color: #333333;;">400</td><td style="text-align: right;color: #333333;;">400</td><td style="text-align: right;color: #333333;;">400</td><td style="text-align: right;color: #333333;;">400</td><td style="text-align: right;color: #333333;;">400</td><td style="text-align: right;color: #333333;;">400</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;;">5</td><td style="text-align: right;color: #333333;;">500</td><td style="text-align: right;color: #333333;;">500</td><td style="text-align: right;color: #333333;;">500</td><td style="text-align: right;color: #333333;;">500</td><td style="text-align: right;color: #333333;;">500</td><td style="text-align: right;color: #333333;;">500</td><td style="text-align: right;color: #333333;;">500</td><td style="text-align: right;color: #333333;;">500</td><td style="text-align: right;color: #333333;;">500</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;;">6</td><td style="text-align: right;color: #333333;;">600</td><td style="text-align: right;color: #333333;;">600</td><td style="text-align: right;color: #333333;;">600</td><td style="text-align: right;color: #333333;;">600</td><td style="text-align: right;color: #333333;;">600</td><td style="text-align: right;color: #333333;;">600</td><td style="text-align: right;color: #333333;;">600</td><td style="text-align: right;color: #333333;;">600</td><td style="text-align: right;color: #333333;;">600</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;;">7</td><td style="text-align: right;color: #333333;;">700</td><td style="text-align: right;color: #333333;;">700</td><td style="text-align: right;color: #333333;;">700</td><td style="text-align: right;color: #333333;;">700</td><td style="text-align: right;color: #333333;;">700</td><td style="text-align: right;color: #333333;;">700</td><td style="text-align: right;color: #333333;;">700</td><td style="text-align: right;color: #333333;;">700</td><td style="text-align: right;color: #333333;;">700</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;;">8</td><td style="text-align: right;color: #333333;;">800</td><td style="text-align: right;color: #333333;;">800</td><td style="text-align: right;color: #333333;;">800</td><td style="text-align: right;color: #333333;;">800</td><td style="text-align: right;color: #333333;;">800</td><td style="text-align: right;color: #333333;;">800</td><td style="text-align: right;color: #333333;;">800</td><td style="text-align: right;color: #333333;;">800</td><td style="text-align: right;color: #333333;;">800</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;;">9</td><td style="text-align: right;color: #333333;;">900</td><td style="text-align: right;color: #333333;;">900</td><td style="text-align: right;color: #333333;;">900</td><td style="text-align: right;color: #333333;;">900</td><td style="text-align: right;color: #333333;;">900</td><td style="text-align: right;color: #333333;;">900</td><td style="text-align: right;color: #333333;;">900</td><td style="text-align: right;color: #333333;;">900</td><td style="text-align: right;color: #333333;;">900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;;">10</td><td style="text-align: right;color: #333333;;">1000</td><td style="text-align: right;color: #333333;;">1000</td><td style="text-align: right;color: #333333;;">1000</td><td style="text-align: right;color: #333333;;">1000</td><td style="text-align: right;color: #333333;;">1000</td><td style="text-align: right;color: #333333;;">1000</td><td style="text-align: right;color: #333333;;">1000</td><td style="text-align: right;color: #333333;;">1000</td><td style="text-align: right;color: #333333;;">1000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;;">11</td><td style="text-align: right;color: #333333;;">50</td><td style="text-align: right;color: #333333;;">50</td><td style="text-align: right;color: #333333;;">50</td><td style="text-align: right;color: #333333;;">50</td><td style="text-align: right;color: #333333;;">50</td><td style="text-align: right;color: #333333;;">50</td><td style="text-align: right;color: #333333;;">50</td><td style="text-align: right;color: #333333;;">50</td><td style="text-align: right;color: #333333;;">50</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;;">51</td><td style="text-align: right;color: #333333;;">45</td><td style="text-align: right;color: #333333;;">45</td><td style="text-align: right;color: #333333;;">45</td><td style="text-align: right;color: #333333;;">45</td><td style="text-align: right;color: #333333;;">45</td><td style="text-align: right;color: #333333;;">45</td><td style="text-align: right;color: #333333;;">45</td><td style="text-align: right;color: #333333;;">45</td><td style="text-align: right;color: #333333;;">45</td></tr></tbody></table><p style="width:4,8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B5</th><td style="text-align:left">=IF(<font color="Blue">B1>10,B1,1</font>)*INDEX(<font color="Blue">F2:N13,MATCH(<font color="Red">B1,E2:E13,1</font>),MATCH(<font color="Red">A1,F1:N1,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

pasjauo

New Member
Hi,

Something like this:
ABCDEFGHIJKLMN
1386Weight/zone123456789
2zoneweight1100100100100100100100100100
32200200200200200200200200200
43300300300300300300300300300
538704400400400400400400400400400
65500500500500500500500500500
76600600600600600600600600600
87700700700700700700700700700
98800800800800800800800800800
109900900900900900900900900900
1110100010001000100010001000100010001000
1211505050505050505050
1351454545454545454545

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
B5=IF(B1>10,B1,1)*INDEX(F2:N13,MATCH(B1,E2:E13,1),MATCH(A1,F1:N1,0))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
Thank you for your reply. In this case I might have written the calculation in a weird way. Let me explain.

Your designated weight is 86. What the calculation needs to do is take the maximum price that is available as a "full" price. You are missing a "full" price at 50 and again at the next stop. This means that the full price for weight 50 will be price of 10 + (price per unit * 40) which in this case is 1000+(50*40) = 3000. Then it is easy to calculate 86 weight which is max full price (50 weight which is 3000) and the added price per unit until designated price. In your example would this mean 3000+(45*36)=4620. It can be a bit confusing but this is how this works for the client.

That is why i suggested that we use cells as step-counters before the final calculation. Meaning that we can use a cell to point to where the calculation should start. In this case <=10, <50, <100 (if thats the final stop). In this case we can name the parameters 0,1,2. If the cell is 0 we know we should look for a final price in the F2:N11 matrix. If its 1 (we say weight/B1 is 15), we need to find the right zone (in this case 3) and get the H11 value and do the calculation which will be =H11+((B1-E11)*50). Likewise with the 2 counter which is <100. Hopefully you understand what i mean. I will try myself to use some of the match/index formula to see if I can make it grab the right cell for this calculation.
 

jorismoerings

Well-known Member
Hi,

Last attempt:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Calculation table</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Weight/zone</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">10</td><td style="text-align: right;;">50</td><td style="text-align: right;;">200</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";"></td><td style="text-align: right;;">3000</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">200</td><td style="text-align: right;;">200</td><td style="text-align: right;;">200</td><td style="text-align: right;;">200</td><td style="text-align: right;;">200</td><td style="text-align: right;;">200</td><td style="text-align: right;;">200</td><td style="text-align: right;;">200</td><td style="text-align: right;;">200</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">0</td><td style="text-align: right;;">36</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;">300</td><td style="text-align: right;;">300</td><td style="text-align: right;;">300</td><td style="text-align: right;;">300</td><td style="text-align: right;;">300</td><td style="text-align: right;;">300</td><td style="text-align: right;;">300</td><td style="text-align: right;;">300</td><td style="text-align: right;;">300</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";"></td><td style="text-align: right;;">45</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">400</td><td style="text-align: right;;">400</td><td style="text-align: right;;">400</td><td style="text-align: right;;">400</td><td style="text-align: right;;">400</td><td style="text-align: right;;">400</td><td style="text-align: right;;">400</td><td style="text-align: right;;">400</td><td style="text-align: right;;">400</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;">500</td><td style="text-align: right;;">500</td><td style="text-align: right;;">500</td><td style="text-align: right;;">500</td><td style="text-align: right;;">500</td><td style="text-align: right;;">500</td><td style="text-align: right;;">500</td><td style="text-align: right;;">500</td><td style="text-align: right;;">500</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">zone</td><td style=";">weight</td><td style="text-align: right;;">Amount</td><td style="text-align: right;;"></td><td style="text-align: right;;">6</td><td style="text-align: right;;">600</td><td style="text-align: right;;">600</td><td style="text-align: right;;">600</td><td style="text-align: right;;">600</td><td style="text-align: right;;">600</td><td style="text-align: right;;">600</td><td style="text-align: right;;">600</td><td style="text-align: right;;">600</td><td style="text-align: right;;">600</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">3</td><td style="text-align: right;;">86</td><td style="text-align: right;;"> € 4.620,00 </td><td style="text-align: right;;"></td><td style="text-align: right;;">7</td><td style="text-align: right;;">700</td><td style="text-align: right;;">700</td><td style="text-align: right;;">700</td><td style="text-align: right;;">700</td><td style="text-align: right;;">700</td><td style="text-align: right;;">700</td><td style="text-align: right;;">700</td><td style="text-align: right;;">700</td><td style="text-align: right;;">700</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">8</td><td style="text-align: right;;">800</td><td style="text-align: right;;">800</td><td style="text-align: right;;">800</td><td style="text-align: right;;">800</td><td style="text-align: right;;">800</td><td style="text-align: right;;">800</td><td style="text-align: right;;">800</td><td style="text-align: right;;">800</td><td style="text-align: right;;">800</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">9</td><td style="text-align: right;;">900</td><td style="text-align: right;;">900</td><td style="text-align: right;;">900</td><td style="text-align: right;;">900</td><td style="text-align: right;;">900</td><td style="text-align: right;;">900</td><td style="text-align: right;;">900</td><td style="text-align: right;;">900</td><td style="text-align: right;;">900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">10</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">11</td><td style="text-align: right;;">50</td><td style="text-align: right;;">50</td><td style="text-align: right;;">50</td><td style="text-align: right;;">50</td><td style="text-align: right;;">50</td><td style="text-align: right;;">50</td><td style="text-align: right;;">50</td><td style="text-align: right;;">50</td><td style="text-align: right;;">50</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">50</td><td style="text-align: right;;">3000</td><td style="text-align: right;;">3000</td><td style="text-align: right;;">3000</td><td style="text-align: right;;">3000</td><td style="text-align: right;;">3000</td><td style="text-align: right;;">3000</td><td style="text-align: right;;">3000</td><td style="text-align: right;;">3000</td><td style="text-align: right;;">3000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">51</td><td style="text-align: right;;">45</td><td style="text-align: right;;">45</td><td style="text-align: right;;">45</td><td style="text-align: right;;">45</td><td style="text-align: right;;">45</td><td style="text-align: right;;">45</td><td style="text-align: right;;">45</td><td style="text-align: right;;">45</td><td style="text-align: right;;">45</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">200</td><td style="text-align: right;;">12000</td><td style="text-align: right;;">12000</td><td style="text-align: right;;">12000</td><td style="text-align: right;;">12000</td><td style="text-align: right;;">12000</td><td style="text-align: right;;">12000</td><td style="text-align: right;;">12000</td><td style="text-align: right;;">12000</td><td style="text-align: right;;">12000</td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A3</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">A4="",A4=0</font>),"",INDEX(<font color="Red">$F$2:$N$15,MATCH(<font color="Green">$A$2,$E$2:$E$15,1</font>),MATCH(<font color="Green">$A$8,$F$1:$N$1,0</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B3</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">B4=0,B4=""</font>),0,INDEX(<font color="Red">$F$2:$N$15,MATCH(<font color="Green">$B$2,$E$2:$E$15,1</font>),MATCH(<font color="Green">$A$8,$F$1:$N$1,0</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">C4="",C4=0</font>),0,INDEX(<font color="Red">$F$2:$N$15,MATCH(<font color="Green">$C$2,$E$2:$E$15,1</font>),MATCH(<font color="Green">$A$8,$F$1:$N$1,0</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A4</th><td style="text-align:left">=IF(<font color="Blue">A$2=IFERROR(<font color="Red">LOOKUP(<font color="Green">$B8,$A$2:$C$2,$A$2:$C$2</font>),0</font>),$B$8-A$2,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B4</th><td style="text-align:left">=IF(<font color="Blue">B$2=IFERROR(<font color="Red">LOOKUP(<font color="Green">$B8,$A$2:$C$2,$A$2:$C$2</font>),0</font>),$B$8-B$2,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C4</th><td style="text-align:left">=IF(<font color="Blue">C$2=IFERROR(<font color="Red">LOOKUP(<font color="Green">$B8,$A$2:$C$2,$A$2:$C$2</font>),0</font>),$B$8-C$2,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A5</th><td style="text-align:left">=IF(<font color="Blue">A4=0,"",INDEX(<font color="Red">$F$2:$N$15,MATCH(<font color="Green">$B$8,$E$2:$E$15,1</font>),MATCH(<font color="Green">$A$8,$F$1:$N$1,0</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B5</th><td style="text-align:left">=IF(<font color="Blue">B4=0,0,INDEX(<font color="Red">$F$2:$N$15,MATCH(<font color="Green">$B$8,$E$2:$E$15,1</font>),MATCH(<font color="Green">$A$8,$F$1:$N$1,0</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C5</th><td style="text-align:left">=IF(<font color="Blue">C4=0,0,INDEX(<font color="Red">$F$2:$N$15,MATCH(<font color="Green">$B$8,$E$2:$E$15,1</font>),MATCH(<font color="Green">$A$8,$F$1:$N$1,0</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C8</th><td style="text-align:left">=IFERROR(<font color="Blue">(<font color="Red">B3+(<font color="Green">B4*B5</font>)</font>),0</font>)+IFERROR(<font color="Blue">(<font color="Red">C3+(<font color="Green">C4*C5</font>)</font>),0</font>)+IFERROR(<font color="Blue">(<font color="Red">A3+(<font color="Green">A4*A5</font>)</font>),0</font>)+IF(<font color="Blue">B8<=10,INDEX(<font color="Red">$F$2:$N$16,MATCH(<font color="Green">$B$8,$E$2:$E$16,1</font>),MATCH(<font color="Green">$A$8,$F$1:$N$1,0</font>)</font>),0</font>)</td></tr></tbody></table></td></tr></table><br />
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top