How do I get an Arrayformula to lookup an Arrayformula at autofill?

Question

So I'm working on a checkin system where a person's location is checked in to 1 sheet (GPS Coordinates) and the list of acceptable locations are on another sheet. It uses a boundary and tests to see WHICH boundary the person is inside of. Right now the process works with individual equations in each cell but doesn't auto-fill the column when I add Arrayformula. Because I need it to be automatic, I need the autofill location functionality to work.

I've tried a few different options but they all seem to either lock in the values of 1 cell or output an error, or don't autofill. I'm assuming it's because I'm using 2 different arrays on different pages but I don't know how to use or separate the ArrayFormula to work for my purposes.

//Using Specific Cell Values//

=ARRAYFORMULA(
    IF(B2="",,IF(TEXTJOIN(", ", 1, 
    IF((E2*1>=Locations!D$2:D)*
       (E2*1<=Locations!E$2:E)* 
       (F2*1>=Locations!F$2:F)*
       (F2*1<=Locations!G$2:G), 
          Locations!C$2:C, ))="", "out of range", TEXTJOIN(", ", 1, 
    IF((E2*1>=Locations!D$2:D)*
       (E2*1<=Locations!E$2:E)* 
       (F2*1>=Locations!F$2:F)*
       (F2*1<=Locations!G$2:G), 
           Locations!C$2:C, ))))) 


//Using a definite range...//
 
=ARRAYFORMULA(
    IF(B2:B100="",,IF(TEXTJOIN(", ", 1, 
    IF((E2:E100*1>=Locations!D$2:D)*
       (E2:E100*1<=Locations!E$2:E)* 
       (F2:F100*1>=Locations!F$2:F)*
       (F2:F100*1<=Locations!G$2:G), 
          Locations!C$2:C, ))="", "out of range", TEXTJOIN(", ", 1, 
    IF((E2:E100*1>=Locations!D$2:D)*
       (E2:E100*1<=Locations!E$2:E)* 
       (F2:F100*1>=Locations!F$2:F)*
       (F2:F100*1<Locations!G$2:G), 
           Locations!C$2:C, )))))


//Using Infinite Range//

=ARRAYFORMULA(
    IF(B2:B="",,IF(TEXTJOIN(", ", 1, 
    IF((E2:E*1>=Locations!D$2:D)*
       (E2:E*1<=Locations!E$2:E)* 
       (F2:F*1>=Locations!F$2:F)*
       (F2:F*1<=Locations!G$2:G), 
          Locations!C$2:C, ))="", "out of range", TEXTJOIN(", ", 1, 
    IF((E2:E*1>=Locations!D$2:D)*
       (E2:E*1<=Locations!E$2:E)* 
       (F2:F*1>=Locations!F$2:F)*
       (F2:F*1<Locations!G$2:G), 
           Locations!C$2:C, )))))

Also a side note. From what I remember, it all worked fine before I create the "IF" function to keep the cells empty if B-cells were blank (AKA no entry in that row), otherwise the "Locations" column and Arrayformula would populate NA to infinity.

Here's the link to the sheet. https://docs.google.com/spreadsheets/d/1OZSDju3hRyGyRfFhHJT2PLQ3DBvcfOAT1ZvNxB-J0DQ/edit?usp=sharing

Answer 1

Well I came up with something that seems to work. It does use one helper column, but that probably could be eliminated with some more playing around.

I've added a tab, VHCU-GK, to your sheet. The two formulas to generate the results are in N1:O1. The primary formula is as follows:

=ARRAYFORMULA(IF(VALUE(E2:E)>0,IFERROR((ARRAYFORMULA(IF(VLOOKUP(ARRAYFORMULA(VLOOKUP(--E2:E,SORT({Locations!D2:D11,Locations!C2:C11}),2,1)),{Locations!C2:C11,Locations!E2:E11},2,0)>--E2:E,TRUE,FALSE)))
 *
 (ARRAYFORMULA(IF(VLOOKUP(ARRAYFORMULA(VLOOKUP(--F2:F,SORT({Locations!F2:F11,Locations!C2:C11}),2,1)),{Locations!C2:C11,Locations!G2:G11},2,0)>--F2:F,TRUE,FALSE))),0),""))

I'll clean it up shortly, and add some explantion, but it builds on the matrix approach you were taking. It just returns true or false (1/0) based on whether that row's coordinates fall within all four boundaries of a defined location. The second column then does a lookup based on just one boundary, for any row that had all four boundary conditions met. I'm pretty sure these could be combined, but I wanted to give this to you to show a possible array answer to your question.

Let me know if I've missed something, or got something wrong..

EDIT:

I had neglected to include the second formula that makes up part of this answer - it goes in cell O1 of the sample sheet.

={"Valid
Location";
ARRAYFORMULA(IF($N$2:$N=1,
    ARRAYFORMULA(IFERROR(VLOOKUP(--$E$2:$E,SORT({Locations!$D$2:$D$11,Locations!$C$2:$C$11}),2,1),"out of range")),
    IF($N$2:$N=0,
      "out of range",
      "")))}

This formula does a lookup to get the name of the location, for every data row that successfully passed the criteria test, which was the first formula. If the location lookup fails, it is marked as "out of range".

Answer 2

If you are OK with a script/formula hybrid approach, I came up with this:

1. Add this script to your sheet (Tools > Script Editor).

function isCollision(x, y, left, right, bottom, top)
{
  return x >= left && x <= right && y >= bottom && y <= top;
}

/**
 * Check if coordinates are in any of the rectangles and return index based on that.
 *
 * @param {Array<Array<number>>} coords
 * @param {Array<Array<number>>} rects
 * 
 * @return Index of Match, or NaN if no match, which can be used for error checks
 * @customfunction
 */

function InBounds(coords, rects)
{
  let results = [];
  
  for (let i = 0; i < coords.length; i++)
  {
    let x, y;
    [x, y] = coords[i];
    
    let match = -1;
    for (let j = 0; j < rects.length; j++)
    {
      let left, right, bottom, top;
      [left, right, bottom, top] = rects[j];
      
      if (isCollision(x, y, left, right, bottom, top))
      {
        match = j;
        break;
      }
    }
    
    results.push(match === -1 ? NaN : match+1);
  }
  
  return results;
}

This lets you use the InBounds custom function, which accepts arrays as input.

2. Use this formula in your location cell:

=ArrayFormula(IFERROR(
    HLOOKUP("LOCATION",Locations!C:C,ArrayFormula(InBounds(--E2:F,Locations!D2:G))+1),
"out of range"))