0

Is it possible to automatically pull a list of students with 2 or more absences onto another tab in Google Sheets? In the following link, the first tab has a list of students and how many absences they have. I'm wondering if the second tab can be set up to automatically keep a running list of students with two or more days missed. Thank you in advance for your help. I'm trying to learn, and I think the use of formulas and what they can to is really cool, but it just breaks my brain. :-( https://docs.google.com/spreadsheets/d/1wYmOYOZAKsmXCi1z1OUSPk0HBNspJTxSEAgpgAwAENQ/edit?usp=sharing

1
0

What you are looking for is a simple query. I have added a duplicate tab on your sheet called "Kevin Help".

Here is a single formula that pulls both rows (in cell F4):

=query(Sheet1!A4:D19, "select A, D where D >=2")

If the rows move and you are interested in doing this with VLOOKUP, here is the formula in cell A4 that pulls names that have a a total absences of 2 or greater for just the names:

=query(Sheet1!A4:D19, "select A where D >=2")

And here is the formula in cell B4 that fills in the number of absences that the corresponding student in row A has using VLOOKUP with an arrayed range:

=IFERROR(arrayformula(VLOOKUP(A4:A, {Sheet1!A4:A,Sheet1!D4:D},2, FALSE)),"")

3
  • 1
    Thank you so much!!! It's amazing that people like you are out there helping others. The VLOOKUP option seems to be working, overall. I only have two questions: What If I wanted to grab only 2-4 absences, for example? Like >=4 but <5? And how to I keep the query formula from returning #N/A if completed with empty output? I mean to not pick up the data at all if it doesn't agree with the formula? Here is a copy of the actual document I'm working on, without any real names or student numbers: docs.google.com/spreadsheets/d/… Nov 8 at 17:38
  • @SarahSavoy No problem! I don't want to edit your actual sheet without your permission first. But if you say so, I'd be more than happy to help. If you want to constrict the query selection, you could change the second from "select A where D >=2" to "select A where D>=2 and D<=4".
    – Kevin
    Nov 12 at 0:03
  • @SarahSavoy To prevent a query or vlookup array from returning #N/A, wrap the original formula in an iferror. It would go from =query(Sheet1!A4:D19, "select A where D >=2") to =iferror(query(Sheet1!A4:D19, "select A where D >=2"), ""). Those last two quotation marks will contain what you want the formula to show if there is an error or no data. In this case I left it blank, but depending on context, I will sometimes put "No Data" or "0". That is your choice.
    – Kevin
    Nov 12 at 0:05
0

Use query(), like this:

=query(Sheet1!A3:B, "where B >= 2", 1)

To display more columns, and use another column as criteria, use this:

=query(Sheet1!A3:D, "where D >= 2", 1)

See the new Solution sheet in your sample spreadsheet.

1
  • Thank you for taking the time to answer. It's not 100% what I'm looking for, but I probably wasn't clear enough. However, your answer will definitely help me in the future, so I appreciate it! Nov 8 at 20:43

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.