How to VLOOKUP across tables

Hi there,

I am struggling to integrate make multiple lookups in one table to add shipping cost to my overview:

I have on table containing all my Order Data (order number, customer, product, shipping address, price) and I would like to add a column with my shipping cost to easily calculate the profit that I make (Price - Shipping Cost - COGS). This table contains a column with product variant and shipping province.

I have a second table with my associated shipping cost: State, Type of Shipping, Cost of Shipping for each variant - there are 7. (Shipping costs are dependent on State and Variant)

I have a third table with all my variants and associated COGS.

I don’t have an issue integrating my COGS to the Order Data, since those tables are linked.

How do I get my shipping cost? I was able to link Order Data and Shipping Costs via the State column, but how do I avoid integrating all 7 columns as lookups and then write a long IF formula? That kind of makes the additional table useless…

Order Data

  • Order Number
  • Variant
  • State

Shipping Costs

  • State
  • Cost Variant 1
  • Cost Variant 2
  • Cost Variant 3
  • Cost Variant 4
  • Cost Variant 5
  • Cost Variant 6
  • Cost Variant 7

Variants

  • Variant
  • COGS

Any good idea how to structure this?

Thank you!