Excel formule to check the charter invoice

[JGA74]

Please, I need help with an excel formula.
I have an Excel file with 3 tabs: Tab1 - my company-DATA; Tab2 - charter bill; Tab3 - Price.
The first tab is the information of my company's shipments.
The second tab is the information of shipments that a charter delivered for us.
The third tab is the tariff that the charter charges us.

The tab1- “my company-DATA” information has 7 columns.
The column A has the date of the shipment.
The column B has reference number of the shipments,
the column C has the number of parcels(boxes) of the shipment,
the column D has the number of pallets of the shipment,
the column E has the temperature of the shipment,
the column F has the status of the shipment.
the column G must have the price for the shipment that has the status “delivered” on the column F.

The tab2- “charter-bill” information has 6 columns.
The column A has the date of the shipment.
The column B has reference number of the shipments,
the column C has the number of parcels(boxes) of the shipment,
the column D has the number of pallet of the shipment,
the column E has the temperature of the shipment,
the column F has the price.

So the tab 3 – PRICE has 6 columns:
The column A has the number of parcels from 1 till 25,
the column B has the both type of temperature: room and cold,
the column C has the price/tariff of the parcels.
the column E has the number of pallets from 1 till 20.
the column F is the temperature of the pallets price,
the column G has the price/tariff of shipments with pallets.

The price for shipments is per parcel and temperature of per pallets and temperature. And some shipments have pallets and also parcels. So, the total price in this case will be the sum of the price of quantity of parcels and the price of quantity of the pallets.

The parcels shipment has tariff for temperature room (=ambient) and has different tariff for temperature cold.
The pallets shipment has tariff for temperature room(=ambient) and has different tariff for temperature cold.
I need to check with the billing of the charter is correct.

Formula that i need to the column G in the tab1-my company data:
So, on the tab 1 – my company-data, I need to match the reference number of the shipments from charter (on the tab2) with the reference number of the shipments from my company-DATA (tab1).
And from all shipments that have the reference number matched, I must search the price on the third tab-Price that correspond to number of parcels of/and pallets that is on the tab1”my company-data”, since this shipment has the status "delivered" on the column F from my company tab1. If the status is in blank, so the price must be zero.
Because our company only pay the shipments that the status is delivered on our system.

[#2]

@JGA74 The attached workbook contains two possible solutions, depending on your Excel version. Note that I have changed the structure of the pricing tables a bit. See which solution suits you best. 

[#3]

Thanks @Riny_van_Enkele,
That's exactly what I need. I just need one thing: for the price to be blank or equal to zero if the shipment status is also blank.
Because we don't pay for shipments that our system the status of the shipment isn't delivered.

[#4]

@JGA74 

See the attached version (I only did the first solution; the alternative would work the same way)

[#5]

Thanks Hans.

[#6]

Hi @Hans Vogelaar 
A question due to lack of knowledge... what means the "+1" in de formule? Why need to be de C6+1

=IF(F6="delivered"; INDEX(tblParcels;C6+1;MATCH(E6;tblParcels[#Headers];0))+(INDEX(tblPallets;D6+1;MATCH(E6;tblPallets[#Headers];0))); "")

This formula works. Thanks. I would like only to understand the +1.

(...)
On the second formula - one of the bill column gave me the message #N/A for some rows..
Is the " #N/A message"  due to the fact that my company's data reference are not in the charter list?

My work computer has Excel for Microsoft 365 MSO (version 2307)

[#7]

@JGA74 

1) tblParcels and tblPallets begin with 0 parcels and 0 pallets.

So for example 1 parcel corresponds to the 2nd data row of tblParcels.

2) Might be. Could you provide an example where the formula returns #N/A?