Vlookup on Multiple Criteria

[adamski911]

Hi,

I am after a lookup formula to bring across the freight cost to give me a total cost of deliverys?

Please see below table.

Country	City	Catergory	Cost
USA	New York	Inbound Freight	£10
USA	New York	Outbound Freight	£15
USA	New York	Free of Charge	£0
USA	Orlando	Inbound Freight	£15
USA	Orlando	Outbound Freight	£20
USA	Orlando	Free of Charge	£0
USA	Los Angeles	Inbound Freight	£12
USA	Los Angeles	Outbound Freight	£18
USA	Los Angeles	Free of Charge	£0

<colgroup><col width="64" style="width: 48pt;"><colgroup><col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"><colgroup><col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4315;"><colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"><tbody>
</tbody>

Country	City	No of Shipments	Date	Freight	Total Cost
USA	New York	5	01/05/2018	Inbound Freight
USA	New York	1	27/05/2018	Outbound Freight
USA	Orlando	6	03/06/2018	Outbound Freight
USA	Los Angeles	6	14/06/2018	Free of Charge

<colgroup><col width="64" style="width: 48pt;"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4315;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4315;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

Thanks

 

[Special-K99]

in Sheet2!F2
=LOOKUP(2,1/(Sheet1!A$2:A$10=A2)/(Sheet1!B$2:B$10=B2)/(Sheet1!C$2:C$10=E2),(Sheet1!D$2:D$10))
and copy down the column

 

[adamski911]

This is great,however I forgot to mention that I have some tabs that are only inbound freight and only outbound freight etc and was wondering if its possible to incorporate some sort of IF statement so it just returns the price for inbound.ie USA and New York?

 

[Special-K99]

Can you supply some data re these additional tabs?