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