VLOOKUP or MATCH with IF GREATER THAN

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

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)),"")

• 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 '21 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". Nov 12 '21 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. Nov 12 '21 at 0:05

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)