I have been attempting to create an automated lost and found search system using cognito forms, zapier and google sheets. When a form is submitted, zapier creates a new row in the "inquiries" worksheet. I have been able to create formulas that use the input information to search the second worksheet "event data" for matching data but using an ARRAYFORMULA doesn't perform with 100% accuracy. I have used CountIFs to indicate that submitted data cells have a matching counterpart in the database, but I don't think these would be horizontally linked. Despite the unlikely event that multiple pieces of information would occur, perhaps a script or additional arguments would work. When searching for matching date of birth, some times the result is a completely different number.
Here are the most successful formulas I have tried: =arrayformula(IF(A2:A<>"",IF(C2=Index(INDIRECT(A2:A&"!B2:B"),Match(C2,INDIRECT(A2:A&"!B2:B"),0),1),"First Name Found","First Name not yet Found"),""))
=arrayformula(IF(A2:A<>"",CountIFS(L2,"First Name Found",M2,"Last Name Found",N2, "DOB Found")))
https://docs.google.com/spreadsheets/d/17Urx9uJEZhk4mQiEDCf03jptGEqXOm0BdFBu5lEm38Q/edit?usp=sharing
Thanks so much for any solution or guidance!