Vlookup with multiple criteria + substitute if data does not match

[adrienne0914]

I am trying to lookup grand totals depending on Origin and Metalane. I have two sheets. This is the reference table:

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]J[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]K[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]L[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]M[/COLOR]​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD]Sum of Amount[/TD]
[TD][/TD]
[TD]Chargable items[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​

[/TD]
[TD]

Origin​

[/TD]
[TD]

Metalane​

[/TD]
[TD]

AFR fee​

[/TD]
[TD]

AMS​

[/TD]
[TD]

AMS/ACI​

[/TD]
[TD]

CFS charges​

[/TD]
[TD]

Customs clearance fee​

[/TD]
[TD]

Document fee​

[/TD]
[TD]

EDI​

[/TD]
[TD]

ENS​

[/TD]
[TD]

Manifest transfer fee​

[/TD]
[TD]

THC​

[/TD]
[TD]

Grand Total​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

[/TD]
[TD]Dalian[/TD]
[TD]Asia[/TD]
[TD]

30​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

70​

[/TD]
[TD]

200​

[/TD]
[TD]

100​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

400​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​

[/TD]
[TD]Dalian[/TD]
[TD]EU[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

70​

[/TD]
[TD]

200​

[/TD]
[TD]

100​

[/TD]
[TD]

50​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

427​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​

[/TD]
[TD]Dalian[/TD]
[TD]Flat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

70​

[/TD]
[TD]

200​

[/TD]
[TD]

100​

[/TD]
[TD]

50​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

420​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​

[/TD]
[TD]Dalian[/TD]
[TD]NA[/TD]
[TD][/TD]
[TD][/TD]
[TD]

25​

[/TD]
[TD]

70​

[/TD]
[TD]

200​

[/TD]
[TD]

100​

[/TD]
[TD]

50​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

445​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​

[/TD]
[TD]Foshan[/TD]
[TD]Asia[/TD]
[TD]

30​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

30​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​

[/TD]
[TD]Foshan[/TD]
[TD]EU[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

25​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

25​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​

[/TD]
[TD]Foshan[/TD]
[TD]Flat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

60​

[/TD]
[TD]

320​

[/TD]
[TD]

350​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

30​

[/TD]
[TD][/TD]
[TD]

760​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​

[/TD]
[TD]Foshan[/TD]
[TD]NA[/TD]
[TD][/TD]
[TD][/TD]
[TD]

25​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

25​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​

[/TD]
[TD]Foshan[/TD]
[TD]SA[/TD]
[TD][/TD]
[TD]

30​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

30​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​

[/TD]
[TD]Fuzhou[/TD]
[TD]Asia[/TD]
[TD]

30​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

30​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​

[/TD]
[TD]Fuzhou[/TD]
[TD]EU[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

7​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​

[/TD]
[TD]Fuzhou[/TD]
[TD]Flat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

70​

[/TD]
[TD][/TD]
[TD]

200​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

30​

[/TD]
[TD]

300​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​

[/TD]
[TD]Fuzhou[/TD]
[TD]NA[/TD]
[TD][/TD]
[TD][/TD]
[TD]

25​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

25​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​

[/TD]
[TD]Guangzhou[/TD]
[TD]Asia[/TD]
[TD]

30​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

60​

[/TD]
[TD]

320​

[/TD]
[TD]

350​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

760​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR]​

[/TD]
[TD]Guangzhou[/TD]
[TD]EU[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

60​

[/TD]
[TD]

320​

[/TD]
[TD]

350​

[/TD]
[TD][/TD]
[TD]

25​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

755​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]20[/COLOR]​

[/TD]
[TD]Guangzhou[/TD]
[TD]Flat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

60​

[/TD]
[TD]

320​

[/TD]
[TD]

350​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

730​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]21[/COLOR]​

[/TD]
[TD]Guangzhou[/TD]
[TD]NA[/TD]
[TD][/TD]
[TD][/TD]
[TD]

25​

[/TD]
[TD]

60​

[/TD]
[TD]

320​

[/TD]
[TD]

350​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

755​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]22[/COLOR]​

[/TD]
[TD]Guangzhou[/TD]
[TD]SA[/TD]
[TD][/TD]
[TD]

30​

[/TD]
[TD][/TD]
[TD]

60​

[/TD]
[TD]

320​

[/TD]
[TD]

350​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

760​

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet3[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​

[/TD]
[TD="bgcolor: #A6A6A6"]

ORIGIN CFS NAME​

[/TD]
[TD="bgcolor: #A6A6A6"]

DESTINATION COUNTRY​

[/TD]
[TD="bgcolor: #A6A6A6"]

METALANE​

[/TD]
[TD="bgcolor: #DA9694"]

CHINA ORIGIN TOTAL​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]

GUANGZHOU​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]

ARGENTINA​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]

SA​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]

GUANGZHOU​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]

AUSTRALIA​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]

Australia​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]

NANSHA​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]

DENMARK​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]

EU​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]

SHANGHAI​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]

TURKEY​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]

EU​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]

GUANGZHOU​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]

UNITED KINGDOM​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]

EU​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]

GUANGZHOU​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]

UNITED STATES​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]

NA​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]


Sheet1 Columns A and C need to match columns A and B on Sheet3. If they match, insert amount from column M into Column D on Sheet1. If not, insert "Flat" amount from column M. For instance, since there is no "Australia" it will use "Flat" amount.

I hope this makes sense.

 

[adrienne0914]

It's like magic! Thanks so much! You are a life saver, Markmzz!!

 

[markmzz]

It's like magic! Thanks so much! You are a life saver, Markmzz!!

Hi Adrienne!

I'm happy that the formula helps.

And, as I always say, the most important thing is that your problem was solved.

By the way, thank you for the feedback.

Markmzz

 

[adrienne0914]

Okay, now that I'm trying to translate the formula for my actual huge worksheet, I'm running into a problem... SO CLOSE!! The worksheet has many more columns. I'm thinking that this part of the formula is the problem IF(Sheet1!$A$2:$A$6=$A2,Sheet1!$B$2:$E$6) because I'm able to line everything else up. After the $A2, I can't tell which columns it should be pointing to (mine are arranged differently in actual file). Here's my actual formula:

{=VLOOKUP(IF(COUNTIFS(Destination!$C$4:$C$231,$V28,Destination!$D$4:$D$231,$Z28&"*",Destination!$I$4:$I$231,$O28),$O28,"Flat"),IF(Destination!$C$4:$C$231=$V28,IF(ISNUMBER(SEARCH($Z28,Destination!$D$4:$D$231)),Destination!$F$4:$I$231)),MATCH(AB$1,Destination!$C$3:$I$3,0),0)}

Relevant data is in columns C - I. Should this be pointing just to my columns with the dollar amounts (F-H)?

I realize it's difficult [almost impossible] for you to help without seeing it, but my hands are so tied here not being able to use the HTML Jeanie.

 

[markmzz]

Okay, now that I'm trying to translate the formula for my actual huge worksheet, I'm running into a problem... SO CLOSE!! The worksheet has many more columns. I'm thinking that this part of the formula is the problem IF(Sheet1!$A$2:$A$6=$A2,Sheet1!$B$2:$E$6) because I'm able to line everything else up. After the $A2, I can't tell which columns it should be pointing to (mine are arranged differently in actual file). Here's my actual formula:

{=VLOOKUP(IF(COUNTIFS(Destination!$C$4:$C$231,$V28,Destination!$D$4:$D$231,$Z28&"*",Destination!$I$4:$I$231,$O28),$O28,"Flat"),IF(Destination!$C$4:$C$231=$V28,IF(ISNUMBER(SEARCH($Z28,Destination!$D$4:$D$231)),Destination!$F$4:$I$231)),MATCH(AB$1,Destination!$C$3:$I$3,0),0)}

Relevant data is in columns C - I. Should this be pointing just to my columns with the dollar amounts (F-H)?

I realize it's difficult [almost impossible] for you to help without seeing it, but my hands are so tied here not being able to use the HTML Jeanie.

Hi!

Look at your Private Message Box.

Markmzz

 

[markmzz]

Okay, now that I'm trying to translate the formula for my actual huge worksheet, I'm running into a problem... SO CLOSE!! The worksheet has many more columns. I'm thinking that this part of the formula is the problem IF(Sheet1!$A$2:$A$6=$A2,Sheet1!$B$2:$E$6) because I'm able to line everything else up. After the $A2, I can't tell which columns it should be pointing to (mine are arranged differently in actual file). Here's my actual formula:

{=VLOOKUP(IF(COUNTIFS(Destination!$C$4:$C$231,$V28,Destination!$D$4:$D$231,$Z28&"*",Destination!$I$4:$I$231,$O28),$O28,"Flat"),IF(Destination!$C$4:$C$231=$V28,IF(ISNUMBER(SEARCH($Z28,Destination!$D$4:$D$231)),Destination!$F$4:$I$231)),MATCH(AB$1,Destination!$C$3:$I$3,0),0)}

Relevant data is in columns C - I. Should this be pointing just to my columns with the dollar amounts (F-H)?

I realize it's difficult [almost impossible] for you to help without seeing it, but my hands are so tied here not being able to use the HTML Jeanie.

Hi Adrienne!

In this case you can't use VLOOKUP function (Column I is the problem). So try this:

Array Formulas - use Ctrl+Shift+Enter to enter the formula

=INDEX(Destination!$G$2:$H$231,MATCH(IF(COUNTIFS(Destination!$C$2:$C$231,$V28,Destination!$D$2:$D$231,$Z28&"*",Destination!$I$2:$I$231,$O28),$O28,"Flat"),
IF(Destination!$C$2:$C$231=$V28,IF(ISNUMBER(SEARCH($Z28,Destination!$D$2:$D$231)),Destination!$I$2:$I$231)),0),MATCH(AB$1,Destination!$G$1:$H$1,0))

Or

=INDEX(Destination!$G$2:$H$231,MATCH(IF(COUNTIFS(Destination!$C$2:$C$231,$V28,Destination!$D$2:$D$231,$Z28&"*",Destination!$I$2:$I$231,$O28),$O28,"Flat"),
IF(Destination!$C$2:$C$231=$V28,IF(ISNUMBER(SEARCH($Z28,Destination!$D$2:$D$231)),Destination!$I$2:$I$231)),0),MATCH(AB$27,Destination!$G$1:$H$1,0))

Or the Normal Formulas (not Array) below too

=LOOKUP(1,1/((Destination!$C$2:$C$231=$V28)*(ISNUMBER(SEARCH($Z28,Destination!$D$2:$D$231)))*(Destination!$I$2:$I$231=
IF(COUNTIFS(Destination!$C$2:$C$231,$V28,Destination!$D$2:$D$231,$Z28&"*",Destination!$I$2:$I$231,$O28),$O28,"Flat"))),
INDEX(Destination!$G$2:$H$231,,MATCH(AB$1,Destination!$G$1:$H$1,0)))

Or

=LOOKUP(1,1/((Destination!$C$2:$C$231=$V28)*(ISNUMBER(SEARCH($Z28,Destination!$D$2:$D$231)))*(Destination!$I$2:$I$231=
IF(COUNTIFS(Destination!$C$2:$C$231,$V28,Destination!$D$2:$D$231,$Z28&"*",Destination!$I$2:$I$231,$O28),$O28,"Flat"))),
INDEX(Destination!$G$2:$H$231,,MATCH(AB$27,Destination!$G$1:$H$1,0)))

Markmzz

 

[markmzz]

Hi!

Here is the layout of my sheets:

[TABLE="class: grid, width: 1273"]
<tbody>[TR]
[TD][/TD]
[TD]O[/TD]
[TD]V[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[TD]AD[/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]Via port[/TD]
[TD]Destination[/TD]
[TD]charge code[/TD]
[TD]charge notes[/TD]
[TD]Amount[/TD]
[TD]Minimum[/TD]
[TD]Sheet[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Destination[/TD]
[TD]Items[/TD]
[TD][/TD]
[TD]Remarks[/TD]
[TD]Amount[/TD]
[TD]Minimum[/TD]
[TD]Via[/TD]
[TD]Sheet[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]SGSIN, VIA SGSIN[/TD]
[TD]Dalian[/TD]
[TD]CFS[/TD]
[TD][/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]10[/TD]
[TD]Sheet2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Dalian[/TD]
[TD]CFS charges[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]10[/TD]
[TD]SGSIN, VIA SGSIN[/TD]
[TD]Destination[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]SGSIN, VIA SGSIN[/TD]
[TD]Dalian[/TD]
[TD]THC[/TD]
[TD][/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Dalian[/TD]
[TD]THC[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]20[/TD]
[TD]SGSIN, VIA SGSIN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]SGSIN, VIA SGSIN[/TD]
[TD]Dalian[/TD]
[TD]BAF[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]Dalian[/TD]
[TD]BAF[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD]SGSIN, VIA SGSIN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]SGSIN, VIA SGSIN[/TD]
[TD]Dalian[/TD]
[TD]D/O[/TD]
[TD][/TD]
[TD="align: right"]430[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]Dalian[/TD]
[TD]D/O fee[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]430[/TD]
[TD="align: right"]40[/TD]
[TD]SGSIN, VIA SGSIN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]Test[/TD]
[TD]Dalian[/TD]
[TD]D/O[/TD]
[TD][/TD]
[TD="align: right"]433[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]Dalian[/TD]
[TD]CFS charges[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]191[/TD]
[TD="align: right"]1[/TD]
[TD]Flat[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]Test[/TD]
[TD]Dalian[/TD]
[TD]CFS[/TD]
[TD][/TD]
[TD="align: right"]191[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD]Dalian[/TD]
[TD]THC[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]2[/TD]
[TD]Flat[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]Test[/TD]
[TD]Dalian[/TD]
[TD]THC[/TD]
[TD][/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]Dalian[/TD]
[TD]D/O fee[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]433[/TD]
[TD="align: right"]3[/TD]
[TD]Flat[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]****************[/TD]
[TD]***********[/TD]
[TD]************[/TD]
[TD]************[/TD]
[TD]*********[/TD]
[TD]**********[/TD]
[TD]********[/TD]
[TD]**[/TD]
[TD]***[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]**[/TD]
[TD]*********[/TD]
[TD]********[/TD]
[TD]*********[/TD]
[TD]****************[/TD]
[TD]***********[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[adrienne0914]

This is so bizarre. When I copied the layout and first "Normal" formula into a worksheet, it worked. When I transferred the same formula to my document, I got #N/A. I know it's something simple that I'm missing, but I just can't figure it out. You're a real trooper for hanging in there with me though, Markmzz!!

 

[markmzz]

This is so bizarre. When I copied the layout and first "Normal" formula into a worksheet, it worked. When I transferred the same formula to my document, I got #N/A. I know it's something simple that I'm missing, but I just can't figure it out. You're a real trooper for hanging in there with me though, Markmzz!!

Hi!

Look at your Private Message Box.

Markmzz​

 

[markmzz]

This is so bizarre. When I copied the layout and first "Normal" formula into a worksheet, it worked. When I transferred the same formula to my document, I got #N/A. I know it's something simple that I'm missing, but I just can't figure it out. You're a real trooper for hanging in there with me though, Markmzz!!

Hi Adrienne,

Try this small modification (in red) in the formulas:

LOOKUP(1,1/((Destination!$C$3:$C$50=$V28)*(ISNUMBER(SEARCH($Z28,Destination!$D$3:$D$50)))*(Destination!$I$3:$I$50=
IF(COUNTIFS(Destination!$C$3:$C$50,$V28,Destination!$D$3:$D$50,$Z28&"*",Destination!$I$3:$I$50,$O28),$O28,"Flat"))),
INDEX(Destination!$G$3:$H$50,,MATCH(AB$1,Destination!$G$2:$H$2,0)))

Array Formula - use Ctrl+Shift+Enter to enter the formula

=INDEX(Destination!$G$3:$H$50,MATCH(IF(COUNTIFS(Destination!$C$3:$C$50,$V28,Destination!$D$3:$D$50,$Z28&"*",Destination!$I$3:$I$50,$O28),$O28,"Flat"),
IF(Destination!$C$3:$C$50=$V28,IF(ISNUMBER(SEARCH($Z28,Destination!$D$3:$D$50)),Destination!$I$3:$I$50)),0),MATCH(AB$1,Destination!$G$2:$H$2,0))

Ps1: don't forget Automatic Calculation.

Ps2: your private messages box is full.

I hope that this helps.

Markmzz

 

[adrienne0914]

Thanks, Markmzz!! It worked in the small test file that I sent to you, but not in the actual file. I will investigate further when I get to work.

Ps1: You're a rock star!

Ps2: I emptied my private message box. Thanks!