Extract Costs From Post code.

shak1701 

Sometimes, the data needs to be cleaned up to where reasonable formulas can be written. In the attached workbook, see the rows and columns in red on worksheet RateCard2 where I did that.

Row 2 contains the highest quantity for each respective range in row 1. Increase the 999 if you deem it necessary.

I split the rows of rates as needed to get the Post code stubs separate from the 4-digit stubs. Those two types have different values for columns C and D.

The reason for adding leading and trailing commas is because the G Post code stub could inappropriately match the "G" in DG. (That's poor code design. But there might be a historical reason for it.)

Then you can use the formula:

=LET( HU_rounded, ROUND(A2,0),
    stub_suffix, VALUE( MID(C2, LEN(B2)+1, 2) ),
    rate_col, XMATCH( HU_rounded, RateCard2!$G$2:$L$2, 1),
    rate_row, FILTER( RateCard2!$G$4:$L$14,
        ISNUMBER(SEARCH( ","&B2&",", RateCard2!$B$4:$B$14 ) )
        * (RateCard2!$C$4:$C$14 <= stub_suffix)
        * (RateCard2!$D$4:$D$14 >= stub_suffix), "not found" ),
    IFERROR( TAKE( DROP(rate_row,0,rate_col-1), 1, 1 ), "not found" )
)

Because of the TAKE and DROP functions, this requires Excel 365 or Excel for the web.