How to check multiple ranges for different values in Google Sheets-CodePudding

I'm trying to search 3 different ranges in a tab, and trying to display Yes if all three values (email address, name, x) are found in those ranges. Basically, trying to have the formula confirm that yes, all three of those inputs are somewhere in those ranges (order doesn't matter).

Maybe I should use query or regexmatch or something? Any help is appreciated

Tried this formula:

=IF(AND('Helper Calculations'!$I:$I=$A$1,'Helper Calculations'!$J:$J=L$1,'Helper Calculations'!$L:$L=$A2),"Yes","No")

Was expecting that if the search term in each of those cells ($A$1, L$1, $A2) is found somewhere in the corresponding ranges, then it would say Yes

CodePudding user response：

You can try with this (you can change the use of asterisks by wrapping in AND:

=IF(COUNTIF('Helper Calculations'!$I:$I,$A$1)*COUNTIF('Helper Calculations'!$J:$J,L$1)*COUNTIF('Helper Calculations'!$L:$L=$A2),"YES,"NO")

CodePudding user response：

try:

=INDEX(IF(('Helper Calculations'!I:I=A1)*
          ('Helper Calculations'!J:J=L1)*
          ('Helper Calculations'!L:L=A2), "Yes", "No"))