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

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

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.