I need to find the rate for a given location and weight, using a lookup table that lists rates per location and weight. The problem is that the weights in the table are formatted like Up to 2 kg, 2.01-3 kg, 3.01-5 kg and so on, while the weights I am looking for are plain numbers like 10.5 and 13.5. How can I map the location-weight tuples to the rates in the lookup table?

where you input a location in the A9 cell and a Weight in the B9 cell and it automatically puts the correct rate in the C9 cell. This can be achieved with the following formula (in the C9 cell):

=ARRAYFORMULA(VLOOKUP(A9,A1:G5,HLOOKUP(B9,{A1:G1;COLUMN(A1:G1)},2,0),0))

which mixes VLOOKUP (you can read more about it here) to fetch the correct Location Code with HLOOKUP (you can read more about it here) to fetch the correct Weight.

CodePudding user response：

This can be done very simply by just nesting one FILTER inside another; as per the answer given by Oriel Castander you could use this formula in cell C9 instead:

=filter(filter(B2:G5,A2:A5=A9),B1:G1=B9)

CodePudding user response：

first you can get the category, use if or regex function or something.

then use vlookup filter to get the value you want:

vlookup("2.01-3 kg",{transpose(Rates!A1:G1),transpose(filter(Rates!A:G,Rates!A:A=A2))},2,0)