Need Help with Two Condition Combination Formula

[bepedicino]

I need some help creating a combination formula that checks for two conditions to be true.

Column A and Column E in my spreadsheet will contain random values of which may or may not be listed below.

My current formula in cell E* checks to see if the value in Cell F* meets the condition of containing one of the following Country Code values

[TABLE="width: 64"]
<tbody>[TR]
[TD]TH[/TD]
[/TR]
[TR]
[TD]ID[/TD]
[/TR]
[TR]
[TD]IN[/TD]
[/TR]
[TR]
[TD]JO[/TD]
[/TR]
[TR]
[TD]LB[/TD]
[/TR]
[TR]
[TD]PK[/TD]
[/TR]
[TR]
[TD]PH[/TD]
[/TR]
[TR]
[TD]RU[/TD]
[/TR]
[TR]
[TD]TN[/TD]
[/TR]
[TR]
[TD]UA[/TD]
[/TR]
[TR]
[TD]UY[/TD]
[/TR]
[TR]
[TD]VE[/TD]
[/TR]
</tbody>[/TABLE]

My current formula is:

=IF(OR(E1="ID", E1="IN",E1="JO", E1="LB", E1="PH",E1="PK", E1="RU",E1="TH", E1="TN", E1="UA",E1="UY",E1="VE"), "GSP", "")

In addition, I also need the formula in Cell F* to check if one of the following HS Number values is present in Cell A1.

3919905060
3926907500
3926909995
4009120050
7009915000
7318290000
7616995090
8302423065
8302496055
8419909580
8481809050
8501312000
8516909000
8537109070
8544300000
9020009000
9405994090
3926904590
4016935050

Therefore, if cell A1 has a country code of “PK” and a HS Number of 7009915000, then cell F* should be “GSP”

However, if Cell E1 has a Country Code of “US” and Cell A1 has a HS Number of 7009915000, then Cell F1 would be blank because both conditions have not been met.

The values in Column A and Column E will be manually entered so they must be hard coded into the Formula.

 

[ranman256]

usage: F1 =IsValidCode(E1)

Public Function IsValidCode(ByVal pvE)
Const kCODES = "TH,ID,IN,JO,LB,PK,PH,RU,TN,UA,UY,VE"
Const kVALS = "3919905060,3926907500,3926909995,4009120050,7009915000,7318290000,7616995090,8302423065,8302496055,8419580,8481809050,8501312000,8516909000,8537109070,8544300000,9020009000,9405994090,3926904590,4016935050"
Dim bE As Boolean, bA As Boolean


bE = InStr(kCODES, pvE) > 0
bA = InStr(kVALS, Range("A1").Value) > 0


If (bA And bE) Then
   IsValidCode = "GSP"
Else
   IsValidCode = ""
End If
End Function

 

[ndsutherland]

Or

=IF(SUM(--(A1={3926907500,3919905060}))+SUM(--(E3={"TH","ID","IN"}))=2,"GSP","")

sorry I was lazy and didn't put in all your numbers and states. You could also use the straight forward

=IF(AND(OR(E1="ID", E1="IN",E1="JO", E1="LB", E1="PH",E1="PK", E1="RU",E1="TH", E1="TN", E1="UA",E1="UY",E1="VE"),OR(A1=3919905060,A1=3926907500)), "GSP", "")

 

[bepedicino]

Thank you very much to the both you; All is working perfectly!