ArrayFormula & Vlookup: exclude the current row from Vlookup range

Question

I'm building a simple booking system using GoogleSheets. Each entry (Row) is a "booking request". The intervals being entered on the sheet (via a form) are fixed to 1, 2 or 3 hours (which makes this a "simple booking system"). I'm validating these "booking requests" based on "overlaps" in Start & End Time in a couple separate columns L, M, N.

Sheet Data

The Formula

      IF(ROW($A:$A)=1,
            "CheckEndDateTimes (1 hour after Start)",
            IF(ISERROR(VLOOKUP($E1:$E+1/24,$G$2:$G,1,FALSE)),
               "",
               "overlaps 1 hour ahead of start"
           )
        )
     )

I'm using ArrayFormula, such that it auto-updates Sheet data as entries are added via a form. I'm using VLOOKUP because it seems to work well with ArrayFormula (as opposed to Index/Match).

The generalized issue:

• Column E contains the search_key values (i.e. $E1:$E) (Start Datetime).
• Column G contains the range (i.e. $G:$G) (End Datetimes), which I am using to compare intervals (1, 2, 3 hours) ADDED to the search_key value (Column E)
• SO far this works fine, EXCEPT that the Row that is being evaluated ALWAYS evaluates to an overlap; clearly, the mere existence of the End Time in the Row being evaluated is always resulting in an overlap.

My Question Is there a way I can EXCLUDE the current row from the range being evaluated in the VLOOKUP function. (I tried adding "<>", as follows, but it results in NO matches being found:

VLOOKUP($E1:$E+1/24,$G$2:$G<>$G1:$G,1,FALSE)

Or even (to test), VLOOKUP($E1:$E+1/24,$G$2:$G<>$G$4,1,FALSE)

Any help would be appreciated, Thank you kindly. :)

Answer 1

On reflection I think you need to use countifs to check for any rows with a matching overlapped time and with a row number not equal to the current row like this:

=ArrayFormula(if(A2:A="",,countifs(C2:C,A2:A+1/24,row(C2:C),"<>"&row(A2:A))))

Then you can test whether the result is non-zero and display a message:

=ArrayFormula(if(A2:A="",,if(countifs(C2:C,A2:A+1/24,row(C2:C),"<>"&row(A2:A)),"overlaps 1 hour",)))

and similarly for 2 and 3 hours.

BTW I don't think row three does overlap at one hour.