Please help me with the following google sheet function.
Basically, the query is pulling data from sheet "Consolidate" where in it match the name and date and then give results in certain cells. What I want is that if the Cell, I3 is having letter A, Cell B3 (where in the actually query resides) should give me "Absent". If Cell I3 is filled with letter O then Cell B3 should give me "OFF", but if nothing is filled then it should move and run the query to find data from sheet "Consolidate"
=IFS(I3 = "A", "ABSENT" , I3 = "O", "OFF", I3 = "","QUERY(Consolidate!$A:$I, "Select C,D,E,F,G,H,I where A = '"&B:B&"' AND B=date '"&TEXT(C1,"yyyy-mm-dd")&"'",0),"")")
With the above function, I am either getting and error or no data results. I have attached a screen shot of the sheet also for better understanding.