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]

Try this:

=VLOOKUP(IF(COUNTIF(Sheet1!$C$2:$C$6,$C2),$C2,$A2&" Flat"),
Sheet1!$C$2:$F$6,MATCH(D$1,Sheet1!$C$1:$F$1,0),0)_{^{<strike>
</strike>}}
Markmzz

OMG!! Finally! Thank you so much. I have been struggling for weeks. Love this forum!

 

[adrienne0914]

Thanks. What follows does not use the combined range.

In D2 of Sheet2, control+shift+enter, not just enter, copy across to E2, and down:

=IFERROR(INDEX(Sheet1!$D$2:$E$6,MATCH($A2,IF(Sheet1!$B$2:$B$6=IF(COUNTIFS(Sheet1!$A$2:$A$6,$A2,Sheet1!$B$2:$B$6,$B2),$B2,"Flat"),
Sheet1!$A$2:$A$6),0),MATCH(D$1,Sheet1!$D$1:$E$1,0)),"not found")

Even though the other solution worked, thanks for this as well. I have another spreadsheet for this!

 

[markmzz]

OMG!! Finally! Thank you so much. I have been struggling for weeks. Love this forum!

You are welcome.

Try the Array Formula below too:

Use Ctrl+Shift+Enter to enter the formula

=IFERROR(VLOOKUP(IF(COUNTIFS(Sheet1!$A$2:$A$6,$A2,Sheet1!$B$2:$B$6,$B2),$B2,"Flat"),
IF(Sheet1!$A$2:$A$6=$A2,Sheet1!$B$2:$E$6),MATCH(C$1,Sheet1!$B$1:$E$1,0),0),"")

Markmzz

 

[adrienne0914]

Thanks. What follows does not use the combined range.

In D2 of Sheet2, control+shift+enter, not just enter, copy across to E2, and down:

=IFERROR(INDEX(Sheet1!$D$2:$E$6,MATCH($A2,IF(Sheet1!$B$2:$B$6=IF(COUNTIFS(Sheet1!$A$2:$A$6,$A2,Sheet1!$B$2:$B$6,$B2),$B2,"Flat"),Sheet1!$A$2:$A$6),0),MATCH(D$1,Sheet1!$D$1:$E$1,0)),"not found")

I am trying to use this formula for a different worksheet. This time, I need to search for a string within a cell as one of my matches, in addition to the "flat" rate.

Sheet1:
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Destination[/TD]
[TD]Items[/TD]
[TD]Via[/TD]
[TD]Remarks[/TD]
[TD]Amount[/TD]
[TD]Minimum[/TD]
[/TR]
[TR]
[TD]Dalian[/TD]
[TD]CFS charges[/TD]
[TD]SGSIN, VIA SGSIN[/TD]
[TD][/TD]
[TD]190[/TD]
[TD]190[/TD]
[/TR]
[TR]
[TD]Dalian[/TD]
[TD]THC[/TD]
[TD]SGSIN, VIA SGSIN[/TD]
[TD][/TD]
[TD]80[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]Dalian[/TD]
[TD]BAF[/TD]
[TD]SGSIN, VIA SGSIN[/TD]
[TD][/TD]
[TD]30[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Dalian[/TD]
[TD]D/O fee[/TD]
[TD]SGSIN, VIA SGSIN[/TD]
[TD][/TD]
[TD]430[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dalian[/TD]
[TD]CFS charges[/TD]
[TD]Flat[/TD]
[TD][/TD]
[TD]190[/TD]
[TD]190[/TD]
[/TR]
[TR]
[TD]Dalian[/TD]
[TD]THC[/TD]
[TD]Flat[/TD]
[TD][/TD]
[TD]80[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]Dalian[/TD]
[TD]D/O fee[/TD]
[TD]Flat[/TD]
[TD][/TD]
[TD]430[/TD]
[TD]430[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2:
[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]via port[/TD]
[TD]destination[/TD]
[TD]charge
code[/TD]
[TD]charge notes[/TD]
[TD]entered
amount[/TD]
[TD]minimum
amount[/TD]
[/TR]
[TR]
[TD]SGSIN, VIA SGSIN[/TD]
[TD]Dalian[/TD]
[TD]CFS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SGSIN, VIA SGSIN[/TD]
[TD]Dalian[/TD]
[TD]THC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SGSIN, VIA SGSIN[/TD]
[TD]Dalian[/TD]
[TD]BAF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SGSIN, VIA SGSIN[/TD]
[TD]Dalian[/TD]
[TD]D/O[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need to populate the amount and minimum on Sheet2 based on the criteria in columns 1, 2 and 3 of Sheet1. Also note that Items and charge code are not an exact match. So I need to search for the text string as well.

Here is my attempt at the formula (without searching for the text string):

{=IFERROR(INDEX(Sheet1!$E$2:$E$8,MATCH($B2,IF(Sheet1!$A$2:$A$8=IF(COUNTIFS(Sheet1!$C$2:$C$8,
$A2),0),Sheet1!$A$2:$A$8),0),MATCH($C2,Sheet1!$B$2:$B$8,0)),"0")}

I'm pretty sure it's not close. I get confused with nested formulas. Please help.

 

[Aladin Akyurek]

Care to post the values that must obtain for entered amount and minimum amount, given the data you posted from Sheet1?

 

[adrienne0914]

Sorry! Values should be 190 and 190 for the first line, 80 and 80 for second line, etc.

 

[markmzz]

Sorry! Values should be 190 and 190 for the first line, 80 and 80 for second line, etc.

Hi Adrienne!

Try the Array Formula below in E2 (Sheet2) and copy down and to the right

Use Ctrl+Shift+Enter to enter the formula

=VLOOKUP(IF(COUNTIFS(Sheet1!$A$2:$A$8,$B3,Sheet1!$B$2:$B$8,$C3&"*"),$A3,"Flat"),
IF(Sheet1!$A$2:$A$8=$B3,IF(ISNUMBER(SEARCH($C3,Sheet1!$B$2:$B$8)),Sheet1!$C$2:$F$8)),MATCH(E$2,Sheet1!$C$1:$F$1,0))


[TABLE="class: grid, width: 1238"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/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]Via[/TD]
[TD]Remarks[/TD]
[TD]Amount[/TD]
[TD]Minimum[/TD]
[TD]Sheet[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]SGSIN, VIA SGSIN[/TD]
[TD]Dalian[/TD]
[TD]CFS[/TD]
[TD][/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]190[/TD]
[TD]Sheet2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Dalian[/TD]
[TD]CFS charges[/TD]
[TD]SGSIN, VIA SGSIN[/TD]
[TD][/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]190[/TD]
[TD]Sheet1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]SGSIN, VIA SGSIN[/TD]
[TD]Dalian[/TD]
[TD]THC[/TD]
[TD][/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Dalian[/TD]
[TD]THC[/TD]
[TD]SGSIN, VIA SGSIN[/TD]
[TD][/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/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]SGSIN, VIA SGSIN[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]SGSIN, VIA SGSIN[/TD]
[TD]Dalian[/TD]
[TD]D/O[/TD]
[TD][/TD]
[TD="align: right"]430[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]Dalian[/TD]
[TD]D/O fee[/TD]
[TD]SGSIN, VIA SGSIN[/TD]
[TD][/TD]
[TD="align: right"]430[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Test[/TD]
[TD]Dalian[/TD]
[TD]D/O[/TD]
[TD][/TD]
[TD="align: right"]430[/TD]
[TD="align: right"]430[/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]Dalian[/TD]
[TD]CFS charges[/TD]
[TD]Flat[/TD]
[TD][/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]190[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Test[/TD]
[TD]Dalian[/TD]
[TD]CFS[/TD]
[TD][/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]190[/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD]Dalian[/TD]
[TD]THC[/TD]
[TD]Flat[/TD]
[TD][/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Test[/TD]
[TD]Dalian[/TD]
[TD]THC[/TD]
[TD][/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]Dalian[/TD]
[TD]D/O fee[/TD]
[TD]Flat[/TD]
[TD][/TD]
[TD="align: right"]430[/TD]
[TD="align: right"]430[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/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]
[/TR]
[TR]
[TD]10[/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]
[/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]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[Aladin Akyurek]

Sorry! Values should be 190 and 190 for the first line, 80 and 80 for second line, etc.

There should be 4 values for entered amount and 4 values for minimum amount. Does the Flat rule hold for this task too?

 

[adrienne0914]

Hi Adrienne!

Try the Array Formula below in E2 (Sheet2) and copy down and to the right

Use Ctrl+Shift+Enter to enter the formula

=VLOOKUP(IF(COUNTIFS(Sheet1!$A$2:$A$8,$B3,Sheet1!$B$2:$B$8,$C3&"*"),$A3,"Flat"),
IF(Sheet1!$A$2:$A$8=$B3,IF(ISNUMBER(SEARCH($C3,Sheet1!$B$2:$B$8)),Sheet1!$C$2:$F$8)),MATCH(E$2,Sheet1!$C$1:$F$1,0))

Markmzz

The Flat rule does still apply. Yes, there should be 4 values for each amount. I got #N/A with the formula. I put the formula in E2 so the this portion of the formula MATCH(E$2,Sheet1!$C$1:$F$1,0) may be part of the reason. The rest of the formula is pointing to the next line, but I fixed that.

 

[markmzz]

Hi Adrienne!

Try the Array Formula below in E2 (Sheet2) and copy down and to the right

Use Ctrl+Shift+Enter to enter the formula

=VLOOKUP(IF(COUNTIFS(Sheet1!$A$2:$A$8,$B3,Sheet1!$B$2:$B$8,$C3&"*"),$A3,"Flat"),
IF(Sheet1!$A$2:$A$8=$B3,IF(ISNUMBER(SEARCH($C3,Sheet1!$B$2:$B$8)),Sheet1!$C$2:$F$8)),MATCH(E$2,Sheet1!$C$1:$F$1,0))

Markmzz

Hi!

Sorry, a small modification (in red) in the formula above:

=VLOOKUP(IF(COUNTIFS(Sheet1!$A$2:$A$8,$B2,Sheet1!$B$2:$B$8,$C2&"*",Sheet1!$C$2:$C$8,$A2),$A2,"Flat"),
IF(Sheet1!$A$2:$A$8=$B2,IF(ISNUMBER(SEARCH($C2,Sheet1!$B$2:$B$8)),Sheet1!$C$2:$F$8)),MATCH(E$1,Sheet1!$C$1:$F$1,0),0)

Markmzz