Hi someko,
See attachement. And few comments:
- To effectively select one weight group it is better to have only lower or upper limit for the group (not the range 0-250) written in a cell. Then you can use formulas like VLOOKUP, HLOOKUP or INDEX...MATCH
- If data is real then there is no point to make such complicated table - rate per kg is flat in all cases.
- I made separate calculations for weight based and pallet based freight cost - of course it can be done as one long formula.
- I included in calculations also minimum freight column (you have not mentioned in your post but it was in the table).
- In a real life, the table with freight cost and actual calculations would be on separate sheets.
- I used the list-based data validation for cell with destination
- Note that choosen method cell (A16) is obtained by formula:
The main formula for blue table (B14) is:
and for the yellow one (B15):
PS. In your profile you have Excel 2003 while attached file was xlsx - so probably it is no longer 2003 Anyway, the formula works also on Ex2000 and probably earlier versions too.
Bookmarks