Find a specefic cell based on several cretiera

[santnok]

Hi

I have a tabel that I trying to use INDEX MATCH function in, but I'am not sure that I can use that.
I have a tabel that shows zip codes weight and price, but the tabel are set litlebit strange.
Will try to explain. In the tabel below, you see a shipping price tabel.

The grey fields are weight C1:H2 and one range of weihgt are C1:C2 and that is from 1 kilogram to 10 kilogram.
Next range of weight are in D1:D2 and so on to H1:H2 that are from 31 kilogram to 35 kilogram.

The same is for ZIP code marked green fields. Row 3 is for the range of ZIP code 0001 to 1429. Row 4 is for ZIP code from 1430 to 1469 and so on to row 32.

Now for the blue fields, that is cells from C3 to H32 that is the price.

So the task is this:

I will write a ZIP code in K4 and a Weight in cell K5. In the cell K6 the price will automatic be resolved by a formula that
i dont know how it will work. I have tryid litlebit with INDEX, MATCH but since the tabel have more then 1 colum of ZIP codes
and more then one row with weight, Iam not sure how to handel this.

Becouse we have to check if the ZIP code are between or the same A3 and B3 or if the ZIP code are in a different row
We also have to check if the weight are between or the same in C1 and C2 or if the weigt are in another column.

The price in K6 is from the cell E4. That is the right answer in this case.

Hope somone can help me out with a formula here, thanks for all answers

If the formula is better to write in vba, we can do that.

Best reagrds
Roy

 

[Snakehips]

@santnok Maybe with the following Array formula which you will need to confirm with Ctrl+Shift+Enter

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	From	Kg	1	11	16	21	26	31
2	To	Kg	10	15	20	25	30	35
3	0001	1429	150	195	235	275	315	365
4	1430	1469	150	195	235	275	315	375		Zip Code	1448
5	1470	1476	150	195	235	275	315	375		Weight	17
6	1477	1479	150	195	235	275	315	375		Price	235
7	1480	1481	150	195	235	275	315	375
8	1482	1970	150	195	235	275	315	375
9	2000	2021	150	195	235	275	315	375
10	2022	2409	150	195	235	275	315	375
11	2410	2410	150	195	235	275	315	375
12	2411	2411	150	195	235	275	315	375
13	2412	2416	150	195	235	275	315	375
Sheet15

Cell Formulas
Range	Formula
K6	K6	{=INDEX(C3:H32,MATCH(1,1/(B3:B32>=K4),0),MATCH(1,1/(C2:H2>=K5),0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Hope that helps.

 

[santnok]

Hi Snakehips

This worked perfect Is there a change that you explain the formula for me.

However thank you so much for the help

 

[Snakehips]

Hi santnok, Pleased it worked for you.
Are you aware that you can select elements of a formula, in the formula bar, and press the F9 key to evaluate? Doing this can often illustrate / aid your formulation.
Evaluate elements or whole functions as suits and use the Undo function to re-establish the original formula.

This formula uses INDEX on the C3:H32 range.
The Index Row is determined by MATCH(1,1/(B3:B32>=K4),0)
Use the F9 to evaluate B3:B32>=K4 and you will see an array of the associated Trues and Falses
The first True is where the top end of a zip code range is equal to or greater than the target zip code.
Dividing 1 by the above, 1/( B3:B32>=K4), converts the True and False array values to 1or #DIV/0!
Then, MATCH, exact, looking for 1 will find the first 1 and return the row.
Similar logic is used to find the appropriate Column by looking at the range C2:H2 which holds the top of range weight values.

Hope that helps.

 

[santnok]

Thank you so much for this explanation for this formula I think I understand it