Search for a value with two criterias

P

pasjauo

New Member
Joined
May 1, 2017
Messages
49
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?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Hi,

Just as an example on how to approach:


Book1
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
Sheet3
Cell Formulas
RangeFormula
B5=INDEX(F2:K10,MATCH(A1,E2:E10,0),MATCH(B1,F1:K1,0))
 
Upvote 0
jorismoerings said:
Hi,

Just as an example on how to approach:

ABCDEFGHIJK
Zone / weight
zoneweight

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]600[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0,982241[/TD]
[TD="align: right"]0,152395[/TD]
[TD="align: right"]0,923771[/TD]
[TD="align: right"]0,465143[/TD]
[TD="align: right"]0,561483[/TD]
[TD="align: right"]0,527555[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0,216614[/TD]
[TD="align: right"]0,955084[/TD]
[TD="align: right"]0,924827[/TD]
[TD="align: right"]0,020197[/TD]
[TD="align: right"]0,402408[/TD]
[TD="align: right"]0,694049[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0,763983[/TD]
[TD="align: right"]0,487043[/TD]
[TD="align: right"]0,628271[/TD]
[TD="align: right"]0,114754[/TD]
[TD="align: right"]0,783694[/TD]
[TD="align: right"]0,368298[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0,114754[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0,854205[/TD]
[TD="align: right"]0,882279[/TD]
[TD="align: right"]0,986413[/TD]
[TD="align: right"]0,739743[/TD]
[TD="align: right"]0,892222[/TD]
[TD="align: right"]0,08628[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0,55451[/TD]
[TD="align: right"]0,235303[/TD]
[TD="align: right"]0,087284[/TD]
[TD="align: right"]0,172758[/TD]
[TD="align: right"]0,865933[/TD]
[TD="align: right"]0,178537[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0,673269[/TD]
[TD="align: right"]0,267535[/TD]
[TD="align: right"]0,614898[/TD]
[TD="align: right"]0,173497[/TD]
[TD="align: right"]0,953625[/TD]
[TD="align: right"]0,351748[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0,057207[/TD]
[TD="align: right"]0,603249[/TD]
[TD="align: right"]0,588881[/TD]
[TD="align: right"]0,221016[/TD]
[TD="align: right"]0,560819[/TD]
[TD="align: right"]0,031514[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0,972826[/TD]
[TD="align: right"]0,536599[/TD]
[TD="align: right"]0,525604[/TD]
[TD="align: right"]0,82434[/TD]
[TD="align: right"]0,77269[/TD]
[TD="align: right"]0,610188[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0,672433[/TD]
[TD="align: right"]0,208521[/TD]
[TD="align: right"]0,151488[/TD]
[TD="align: right"]0,70969[/TD]
[TD="align: right"]0,078463[/TD]
[TD="align: right"]0,839913[/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]=INDEX(F2:K10,MATCH(A1,E2:E10,0),MATCH(B1,F1:K1,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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.
 
Upvote 0
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:

Book1
ABCDEFGHIJKL
131100Zone / weight1002003004005006001000
2zoneweight10,9822410,1523950,9237710,4651430,5614830,5275550,083482
320,2166140,9550840,9248270,0201970,4024080,6940490,378216
430,7639830,4870430,6282710,1147540,7836940,3682980,669498
50,66949840,8542050,8822790,9864130,7397430,8922220,086280,57495
650,554510,2353030,0872840,1727580,8659330,1785370,53103
760,6732690,2675350,6148980,1734970,9536250,3517480,923199
870,0572070,6032490,5888810,2210160,5608190,0315140,98567
980,9728260,5365990,5256040,824340,772690,6101880,021833
1090,6724330,2085210,1514880,709690,0784630,8399130,614107
Sheet1
Cell Formulas
RangeFormula
B5=INDEX(F2:L10,MATCH(A1,E2:E10,0),MATCH(B1,F1:L1,1))


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.
 
Upvote 0
Thank you very much for your reply.

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

[TABLE="width: 665"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]Weight/zone[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]300[/TD]
[TD]300[/TD]
[TD]300[/TD]
[TD]300[/TD]
[TD]300[/TD]
[TD]300[/TD]
[TD]300[/TD]
[TD]300[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]400[/TD]
[TD]400[/TD]
[TD]400[/TD]
[TD]400[/TD]
[TD]400[/TD]
[TD]400[/TD]
[TD]400[/TD]
[TD]400[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]600[/TD]
[TD]600[/TD]
[TD]600[/TD]
[TD]600[/TD]
[TD]600[/TD]
[TD]600[/TD]
[TD]600[/TD]
[TD]600[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]700[/TD]
[TD]700[/TD]
[TD]700[/TD]
[TD]700[/TD]
[TD]700[/TD]
[TD]700[/TD]
[TD]700[/TD]
[TD]700[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]800[/TD]
[TD]800[/TD]
[TD]800[/TD]
[TD]800[/TD]
[TD]800[/TD]
[TD]800[/TD]
[TD]800[/TD]
[TD]800[/TD]
[TD]800[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]900[/TD]
[TD]900[/TD]
[TD]900[/TD]
[TD]900[/TD]
[TD]900[/TD]
[TD]900[/TD]
[TD]900[/TD]
[TD]900[/TD]
[TD]900[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]Break / 1[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Stop / 50[/TD]
[TD]3000[/TD]
[TD]3000[/TD]
[TD]3000[/TD]
[TD]3000[/TD]
[TD]3000[/TD]
[TD]3000[/TD]
[TD]3000[/TD]
[TD]3000[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]Break / 1[/TD]
[TD]45[/TD]
[TD]45[/TD]
[TD]45[/TD]
[TD]45[/TD]
[TD]45[/TD]
[TD]45[/TD]
[TD]45[/TD]
[TD]45[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]Stop / 200[/TD]
[TD]12000[/TD]
[TD]12000[/TD]
[TD]12000[/TD]
[TD]12000[/TD]
[TD]12000[/TD]
[TD]12000[/TD]
[TD]12000[/TD]
[TD]12000[/TD]
[TD]12000[/TD]
[/TR]
</tbody>[/TABLE]


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.[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi,

Something like this:

Book1
ABCDEFGHIJKLMN
1386Weight/zone123456789
2zoneweight1100100100100100100100100100
32200200200200200200200200200
43300300300300300300300300300
538704400400400400400400400400400
65500500500500500500500500500
76600600600600600600600600600
87700700700700700700700700700
98800800800800800800800800800
109900900900900900900900900900
1110100010001000100010001000100010001000
1211505050505050505050
1351454545454545454545
Sheet1
Cell Formulas
RangeFormula
B5=IF(B1>10,B1,1)*INDEX(F2:N13,MATCH(B1,E2:E13,1),MATCH(A1,F1:N1,0))
 
Upvote 0
jorismoerings said:
Hi,

Something like this:
ABCDEFGHIJKLMN
Weight/zone
zoneweight

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3870[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]700[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]800[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]900[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]=IF(B1>10,B1,1)*INDEX(F2:N13,MATCH(B1,E2:E13,1),MATCH(A1,F1:N1,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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.
 
Upvote 0
Hi,

Last attempt:


Book1
ABCDEFGHIJKLMN
1Calculation tableWeight/zone123456789
210502001100100100100100100100100100
3 300002200200200200200200200200200
403603300300300300300300300300300
5 4504400400400400400400400400400
65500500500500500500500500500
7zoneweightAmount6600600600600600600600600600
8386€ 4.620,007700700700700700700700700700
98800800800800800800800800800
109900900900900900900900900900
1110100010001000100010001000100010001000
1211505050505050505050
1350300030003000300030003000300030003000
1451454545454545454545
15200120001200012000120001200012000120001200012000
Sheet3
Cell Formulas
RangeFormula
A3=IF(OR(A4="",A4=0),"",INDEX($F$2:$N$15,MATCH($A$2,$E$2:$E$15,1),MATCH($A$8,$F$1:$N$1,0)))
A4=IF(A$2=IFERROR(LOOKUP($B8,$A$2:$C$2,$A$2:$C$2),0),$B$8-A$2,0)
A5=IF(A4=0,"",INDEX($F$2:$N$15,MATCH($B$8,$E$2:$E$15,1),MATCH($A$8,$F$1:$N$1,0)))
B3=IF(OR(B4=0,B4=""),0,INDEX($F$2:$N$15,MATCH($B$2,$E$2:$E$15,1),MATCH($A$8,$F$1:$N$1,0)))
B4=IF(B$2=IFERROR(LOOKUP($B8,$A$2:$C$2,$A$2:$C$2),0),$B$8-B$2,0)
B5=IF(B4=0,0,INDEX($F$2:$N$15,MATCH($B$8,$E$2:$E$15,1),MATCH($A$8,$F$1:$N$1,0)))
C3=IF(OR(C4="",C4=0),0,INDEX($F$2:$N$15,MATCH($C$2,$E$2:$E$15,1),MATCH($A$8,$F$1:$N$1,0)))
C4=IF(C$2=IFERROR(LOOKUP($B8,$A$2:$C$2,$A$2:$C$2),0),$B$8-C$2,0)
C5=IF(C4=0,0,INDEX($F$2:$N$15,MATCH($B$8,$E$2:$E$15,1),MATCH($A$8,$F$1:$N$1,0)))
C8=IFERROR((B3+(B4*B5)),0)+IFERROR((C3+(C4*C5)),0)+IFERROR((A3+(A4*A5)),0)+IF(B8<=10,INDEX($F$2:$N$16,MATCH($B$8,$E$2:$E$16,1),MATCH($A$8,$F$1:$N$1,0)),0)
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Performance issue using a combination of dropdowns & lookups
Replies
9
Views
501
Akuini
Akuini
H
  • Question Question
Lookup value from matrix based on 3 search parameters
Replies
5
Views
508
Alex Blakenburg
A
S
  • Question Question
Return a value based on two criterias
Replies
1
Views
787
RasGhul
RasGhul
E
  • Question Question
VBA growing a tree from a seed to use DFS to traverse
Replies
0
Views
2K
equinox
E
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top