If values in one row match the criteria, compare it with another value for a match and count

Question

Please check both links.

Sheet1 Sheet2

What I'm trying to accomplish is...

On Sheet1, check each row for A="MSP1", B="AZNG", E=blank and F1="FULL" then check column D in Sheet1 against column C in Sheet2.

If there is a partial match, such as in the example "V210363" and "AZNG V210363" then count it as 1 for each entry that fits this criteria.

Note, it's not an exact match as the number is preceded by a 4 letter string and a space.

Answer 1

Please try the function below in Sheet1. The function will return TRUE if all conditions are matched, otherwise FALSE.

=IF(AND(A2="MSP1",B2="AZNG",E2="",F2="FULL"),NOT(ISERROR(MATCH("*"&D2&"*",Sheet2!$C$2:$C$15,0))))

The formula checks A, B, E, F columns first and if conditions fulfilled then checks if searching D in Sheet2's C will return an error or not.