Excel multiple criteria

[David Montoya]

Dear,
I am looking for your assistance again. I have the below formula in column “L” of an Excel spreadsheet, which provides different results depending on various criteria, such as:
1) If Master DB, column “D” (HTS) is contained in SPI by HTS column “A”…

a) If no, leave the cell “blank”​

b) If yes,​

2) The Master DB column “G” (CountryofOrigin) needs to be checked

a) If it appears in column “B” (GSP Countries), and​

i) It is not in “SPI by HTS” column “D”(GSP Country Excluded), then​

ii) The result should be the result of VLOOKUP of “Master DB” column “D”(HTS) against “SPI by HTS” column “B”(SPI) value​

b) Or if it appears in “GSP Countries”, and​

i) It is in “SPI by HTS” column “D”(GSP Country Excluded), then​

ii) The result should be the result of VLOOKUP of “Master DB” column “D”(HTS) against “SPI by HTS” column “B”(SPI) value minus any variation of A, A*, or A+​

3) The Master DB column “G” (CountryofOrigin) needs to be checked again

a) If it appears in column “B” (GSP Countries), and​

b) The Master DB column “D”(HTS) appears in SPI by HTS column “A” (HTS)​

i) The result should be the result of VLOOKUP of “Master DB” column “D”(HTS) against “SPI by HTS” column “B”(SPI), except​

ii) If the Master DB, Colum “G”(CountryofOrigin) appears in “SPI by HTS” column “D”(GSP Country Excluded) then​

iii) Leave the cell blank

​

The following formula is giving the desired results, except per (see “FALSE” results in Master DB, column “L” (SPICode)) when applying step 3) above.

=IF($A2="","",
IF(COUNTIF('SPI by HTS'!$A:$A,"*"&'Master DB'!D2&"*")<1,"",
IF(AND(COUNTIF('GSP Countries'!$B:$B,"*"&'Master DB'!G2&"*"),(COUNTIF('SPI by HTS'!E:E,"*"&'Master DB'!D2&'Master DB'!G2&"*")<1)),
VLOOKUP(D2,'SPI by HTS'!A:B,2,0),IF(COUNTIF('GSP Countries'!B:B,"*"&'Master DB'!G2&"*")<1,IF(VLOOKUP(D2,'SPI by HTS'!A:B,2,0)="C A*","C",
IF(VLOOKUP(D2,'SPI by HTS'!A:B,2,0)="C A","C",IF(VLOOKUP(D2,'SPI by HTS'!A:B,2,0)="C A+","C"))),
IF(AND(COUNTIF('GSP Countries'!$B:$B,"*"&'Master DB'!G2&"*"),(COUNTIF('SPI by HTS'!E:E,"*"&'Master DB'!D2&'Master DB'!G2&"*")>0)),
IF(VLOOKUP(D2,'SPI by HTS'!A:B,2,0)="C A*","C",
IF(VLOOKUP(D2,'SPI by HTS'!A:B,2,0)="C A","C",
IF(VLOOKUP(D2,'SPI by HTS'!A:B,2,0)="C A+","C"))),
VLOOKUP(D2,'SPI by HTS'!A:B,2,0))))))

Thanks if you can assist.