• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

Lookup value in table

Hello Experts,

The attached file has a table with air freight prices.

I'm looking for compact way to look up the correct cost/kg  based on the Airport and Weight.

Thanks!
shipping-table.xlsx
0
tomfolinsbee
Asked:
tomfolinsbee
2 Solutions
 
Saqib Husain, SyedEngineerCommented:
Try this file using match and lookup and after modifying the weights header
Copy-of-shipping-table.xlsx
0
 
Patrick MatthewsCommented:
1) In E1:J1, change your weight thresholds to be actual numbers, e.g. -45, 45, 100, 300, 500, 1000

2) Apply the following custom number format to E1:J1 if you still want them to display in kg:

+0.0"kg",-0.0"kg"

3) Use a formula like this to get the shipping rate:

=INDEX(E2:J5,MATCH(O2,C2:C5,0),MATCH(O3,E1:J1))

Sample file attached.
Q-28002736.xlsx
0
 
tomfolinsbeeAuthor Commented:
Thank you!
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now