VLookup column within Query Result Google Sheets

Question

I'm working on a Behavior Chart system for my school within Google Sheets for Grades 4-6. I created a decent system last year, but it relied heavily on vlookups, which were really slow to update, especially as more data was entered.

This year, I'm trying to update it using queries, which are a lot faster, but I'm running into difficulty where I need the query to look up a value in a separate table.

So, I have it to where it lists the students who received a Demerit and also shows the count of how many demerits they have on the current date:

=query('Form Responses 1'!A:D,"
select C,count(C)
where A>=  datetime '"&TEXT(today(),"yyyy-mm-dd HH:mm:ss")&"' and 
A<  datetime '"&TEXT(today()+time(12,0,0),"yyyy-mm-dd HH:mm:ss")&"' and D != '6 - Late Assignments (WE)'
group by C order by count(C) desc
label C 'Student Name', count(C) 'Number of Marks'")

But now I need a third column whose result is based on the count. Our behavior system at school is such that if a child receives 1 mark, it's a Warning, 2 it's a recess penalty, etc. Every grade has a different set of consequences, so I need for it to look up the data from a separate table and display what consequence the child is to receive. Normally I would resolve this by using a vlookup or choose function, but I don't know how to implement that in a query, or if I would need to use a nested query?

So, right now my results look like this:

Student Name | Number of Marks
Abby Alpha | 1
Benny Beta | 2
Gort Gamma | 27

But I need for it to return something like:

Student Name | Number of Marks | Consequence
Abby Alpha | 1  | Warning
Benny Beta | 2  | Walking at Recess
Gort Gamma | 7 | Office

where the Consequence is determined based on looking up the result the second column against a simple table and if the Number of Marks exceeds the max number in the table, it should return the maximum possible result.

I'd appreciate any advice! I'm only an elementary teacher, so while it's fun figuring it out, it's a bit outside my comfort zone. Thanks!

Answer 1

try:

=ARRAYFORMULA(IFNA(VLOOKUP(B2:B, E:F, 2, 0)))

---

update:

paste in C3:

=ARRAYFORMULA(IFNA(VLOOKUP(B3:B, Sheet3!A:B, 2, 0)))

paste in F3:

=ARRAYFORMULA(IFNA(VLOOKUP(E3:E, Sheet3!A:B, 2, 0)))

---

update 2:

=ARRAYFORMULA({QUERY('Form Responses 1'!A:C,
 "select C,count(C)
  where A >= datetime '"&TEXT(TODAY(), "yyyy-mm-dd hh:mm:ss")&"'  
    and A <  datetime '"&TEXT(TODAY()+TIME(12, 0, 0), "yyyy-mm-dd hh:mm:ss")&"'
  group by C
  label count(C)'Number of Marks'"), IFNA(VLOOKUP(QUERY('Form Responses 1'!A:C,
 "select count(C)
  where A >= datetime '"&TEXT(TODAY(), "yyyy-mm-dd hh:mm:ss")&"'  
    and A <  datetime '"&TEXT(TODAY()+TIME(12, 0, 0), "yyyy-mm-dd hh:mm:ss")&"'
  group by C
  label count(C)'Number of Marks'"), 
 {"Number of Marks", "Consequence"; Sheet3!A:B}, 2, 0))})