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.


[TABLE="width: 253"]
<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>[TR]
[TD="width: 64, bgcolor: transparent"]Country[/TD]
[TD="width: 80, bgcolor: transparent"]City[/TD]
[TD="width: 118, bgcolor: transparent"]Catergory[/TD]
[TD="width: 75, bgcolor: transparent"]Cost[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]USA[/TD]
[TD="bgcolor: transparent"]New York[/TD]
[TD="bgcolor: transparent"]Inbound Freight[/TD]
[TD="bgcolor: transparent, align: right"]£10[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]USA[/TD]
[TD="bgcolor: transparent"]New York[/TD]
[TD="bgcolor: transparent"]Outbound Freight[/TD]
[TD="bgcolor: transparent, align: right"]£15[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]USA[/TD]
[TD="bgcolor: transparent"]New York[/TD]
[TD="bgcolor: transparent"]Free of Charge[/TD]
[TD="bgcolor: transparent, align: right"]£0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]USA[/TD]
[TD="bgcolor: transparent"]Orlando[/TD]
[TD="bgcolor: transparent"]Inbound Freight[/TD]
[TD="bgcolor: transparent, align: right"]£15[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]USA[/TD]
[TD="bgcolor: transparent"]Orlando[/TD]
[TD="bgcolor: transparent"]Outbound Freight[/TD]
[TD="bgcolor: transparent, align: right"]£20[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]USA[/TD]
[TD="bgcolor: transparent"]Orlando[/TD]
[TD="bgcolor: transparent"]Free of Charge[/TD]
[TD="bgcolor: transparent, align: right"]£0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]USA[/TD]
[TD="bgcolor: transparent"]Los Angeles[/TD]
[TD="bgcolor: transparent"]Inbound Freight[/TD]
[TD="bgcolor: transparent, align: right"]£12[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]USA[/TD]
[TD="bgcolor: transparent"]Los Angeles[/TD]
[TD="bgcolor: transparent"]Outbound Freight[/TD]
[TD="bgcolor: transparent, align: right"]£18[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]USA[/TD]
[TD="bgcolor: transparent"]Los Angeles[/TD]
[TD="bgcolor: transparent"]Free of Charge[/TD]
[TD="bgcolor: transparent, align: right"]£0
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 390"]
<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>[TR]
[TD="width: 64, bgcolor: transparent"]Country[/TD]
[TD="width: 80, bgcolor: transparent"]City[/TD]
[TD="width: 118, bgcolor: transparent"]No of Shipments[/TD]
[TD="width: 75, bgcolor: transparent"]Date[/TD]
[TD="width: 118, bgcolor: transparent"]Freight [/TD]
[TD="width: 64, bgcolor: transparent"]Total Cost[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]USA[/TD]
[TD="bgcolor: transparent"]New York[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]01/05/2018[/TD]
[TD="bgcolor: transparent"]Inbound Freight[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]USA[/TD]
[TD="bgcolor: transparent"]New York[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]27/05/2018[/TD]
[TD="bgcolor: transparent"]Outbound Freight[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]USA[/TD]
[TD="bgcolor: transparent"]Orlando[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]03/06/2018[/TD]
[TD="bgcolor: transparent"]Outbound Freight[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]USA[/TD]
[TD="bgcolor: transparent"]Los Angeles[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]14/06/2018[/TD]
[TD="bgcolor: transparent"]Free of Charge[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

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?