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!