Partial Lookup Text with variable lookup values

Hi All,

I am trying to create a forumla that will tell me on my customer order tab that quality wants to review the order based on the product ID and customer. The customer name can vary (Usually by a customer having various addresses). So I want my spreadsheet to return a yes value if the cell contains the customer name and the product ID. I want to create a blank tab that our quality team can populate data into that will pull in the customer order tab. So my formula below works just fine for the cells that I have information entered into, but I want to have blank lines that quality can input data into. If I change the formula below to C$2:$C$500 and A$2:$A$500 then everything returns back a yes. Any idea on how I can modify this so that data can be entered in?


=IF(AND(SUMPRODUCT(--ISNUMBER(SEARCH('Quality List'!$C$2:$C$5,'Customer Orders'!F2)))>0,SUMPRODUCT(--ISNUMBER(SEARCH('Quality List'!$A$2:$A$5,'Customer Orders'!A2)))>0),"Yes","No")

try: which adds a test for zero-length column C in Quality List sheet.
Be aware that doing it this way will find Customer 3 in Customer 33 INC , SAINT PAUL , MN