Google Sheets – Check if Two Cell Values Exist in Two Columns of Another Sheet

google sheetsgoogle-formsvlookup

I am trying to make a class attendance system where the student checks in by submitting her name into google forms. The final result will be displayed in Google sheets by marking a check box after each of the present student´s name.

So, In Google Sheets,in cell B3 of sheet2, I would like write a formula that checks the following :
if the text in B2 of sheet2 (2019-10-20) matches column A of sheet1 where the text contains additional characters (2019-10-20 04:15:25)
AND if the text in A3 of sheet2 (Robin) exists in column B among the results that were just matched.
If so – return TRUE, else return FALSE.

IF(AND… for making several conditions

FIND to find the text 2019-10-20 in a column with additional characters

=IF(AND(COUNT(FIND(B$2;Sheet1!A:A));A3;Sheet1!B:B);TRUE;FALSE)

This is not working but as far as I get.

Example

Best Answer

New solution :

=or(ArrayFormula(
regexmatch(iferror(
FILTER(Sheet1!$B$2:$B$9;int(Sheet1!$A$2:$A$9)=B$2)
);$A3)
))

It filter the original list to pick up which names appears on a determined dates. Then it checks if the name exist in the list.

Using your formula, here's a way to do it : 

=IF(
    AND(
        or(ArrayFormula(B$2=int(Sheet1!$A3:$A10))),
        or(ArrayFormula($A3=Sheet1!$B$3:$B$8)))

    ,true,false)

Expand to right and to the bottom

Some explanation

First condition : or(ArrayFormula(B$2=int(Sheet1!$A3:$A10))

  1. How to match the date even with the additional characters ?

int() ?
The first date format 2019-10-20 is an int, you can check with =value it returns 43758.
2019-10-21 04:15:25 is also an int but with several numbers after the comma so =int() is the quickest way to have a round value like the first date format.

  1. How to check every cells possible ?

Arrayformula will test the range and return someting like this : 

False 
False 
False 
True
False
  1. There's a match

Used or()

the OR function returns true if any of the provided arguments are logically true. The OR function

Related Topic