2

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

Improve this question
2
  • Welcome. Please add a brief description of your search/research efforts as is suggested by How to Ask. – Rubén♦ Oct 21 '19 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 '19 at 21:27
0

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

Improve this answer
6
  • 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 '19 at 17:56
  • do you mind sharing a dummy sheet with sample data with what you did ? – pjmg Oct 22 '19 at 20:25
  • docs.google.com/spreadsheets/d/… Please note that Swedish Google sheets uses ";" instead of ",". – Robin J Oct 23 '19 at 20:03
  • OM=IF OCH=AND HELTAL=INT – Robin J Oct 23 '19 at 20:42
  • 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 '19 at 21:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.