# How to check if the values of two cells exist in two columns of another sheet

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. • Welcome. Please add a brief description of your search/research efforts as is suggested by How to Ask. – Rubén Oct 21 at 20:01
• You beginning of solution is not working because you are working with date format (date format is complicated). I'll try an answer – pjmg Oct 21 at 21:27

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

• Thank you pjrng. However I don´t get it to work with the code you suggested. If the date in sheet2 matches one of the dates in the date-column of sheet1, all boxes on that particular date are checked regardless of the names. So it checks the first condition but not the second condition with matching names. – Robin J Oct 22 at 17:56
• do you mind sharing a dummy sheet with sample data with what you did ? – pjmg Oct 22 at 20:25
• Don't worry the formulas display according to browser language. Anyway, I used another way to resolve it. If this is ok, you can resolve the question and i'll update my answer with the new formula : `=or(ArrayFormula(regexmatch(iferror(FILTER(Sheet1!\$B\$2:\$B\$9;int(Sheet1!\$A\$2:\$A\$9)=B\$2));\$A3)))` : It first filter name based on the date, then check if the name exist in the list. Should be good now. – pjmg Oct 23 at 21:22