# 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:

#### 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

</tbody>
Sheet3

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

</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:

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

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

 Weight/zone 1 2 3 4 5 6 7 8 9 1 100 100 100 100 100 100 100 100 100 2 200 200 200 200 200 200 200 200 200 3 300 300 300 300 300 300 300 300 300 4 400 400 400 400 400 400 400 400 400 5 500 500 500 500 500 500 500 500 500 6 600 600 600 600 600 600 600 600 600 7 700 700 700 700 700 700 700 700 700 8 800 800 800 800 800 800 800 800 800 9 900 900 900 900 900 900 900 900 900 10 1000 1000 1000 1000 1000 1000 1000 1000 1000 Break / 1 50 50 50 50 50 50 50 50 50 Stop / 50 3000 3000 3000 3000 3000 3000 3000 3000 3000 Break / 1 45 45 45 45 45 45 45 45 45 Stop / 200 12000 12000 12000 12000 12000 12000 12000 12000 12000

<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:

#### pasjauo

##### New Member
Hi,

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

</tbody>
Sheet1

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

</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.

Hi,

Last attempt: