Forum Discussion

Patrick2788's avatar
Patrick2788
Silver Contributor
Aug 14, 2026

This discussion has inspired me to create a generalized function capable of locating a 2D array within a 2D array and returning a 2D array from the Moore neighborhood.

Excel has plenty of functions geared towards working with 1D arrays. Lookup functions return 1D arrays and shaping functions can return 2D arrays, but will only accept 1D array inputs.

What if the grid could be left as-is and a generalized function could still pull the return?

 

Consider this demo:

// Signature of draft

Navigate2Dλ=
LAMBDA(
    lookup_kernel,        // 2D array (Will also accept 1D arrays)
    match_array_2D,    // 2D arrays only
    [return_direction], // Moore neighborhood - 1 to 9. 5 is center. If omitted, defaults to 8 (below)
    [return_height],   // If these two parameters are omitted, defaults to height x width of the lookup kernel
    [return_width],

 

It's working smoothly so far. It's locating Cities using convolution windowing.  I'll have more to share when it's finalized.

m_tarler's avatar
m_tarler
Silver Contributor
Aug 17, 2026

So I thought of doing something similar.  I'm not sure how yours works/acts so maybe mine is the same or maybe it is different.  Again, my Excel Labs is still not working on this computer :( so here is the formula based:

GridLookUp, LAMBDA(pattern,lookUpGrid,[returnGrid],[not_found],[step_rows],[step_cols],[options], LET(
   InRows, ROWS(pattern),
   InCols, COLUMNS(pattern),
   OutputArray, IF((COLUMNS(lookUpGrid)=COLUMNS(returnGrid))*(ROWS(lookUpGrid)=ROWS(returnGrid)), 1, 0),
   OutRows, (ROWS(pattern)-1)*OutputArray+1,
   OutCols, (COLUMNS(pattern)-1)*OutputArray+1,
   NFout, IF(ISOMITTED(not_found),"PATTERN NOT FOUND",not_found),
   stepRows, IF(step_rows,step_rows,InRows),
   stepCols, IF(step_cols,step_cols,InCols),
   FindLocations, MAKEARRAY(QUOTIENT(ROWS(lookUpGrid)-InRows,stepRows)+1,QUOTIENT(COLUMNS(lookUpGrid)-InCols,stepCols)+1,LAMBDA(x,y, PRODUCT(--(TAKE(DROP(lookUpGrid,(x-1)*stepRows,(y-1)*stepCols),InRows,InCols)=pattern)))),
   XYcount, SEQUENCE(ROWS(FindLocations)*COLUMNS(FindLocations),,0),
   XYlist, IF({1,0},QUOTIENT(XYcount,COLUMNS(FindLocations)),MOD(XYcount,COLUMNS(FindLocations)))+1,
   XYLocations, IF(SUM(FindLocations),FILTER(XYlist, INDEX(FindLocations,TAKE(XYlist,,1),TAKE(XYlist,,-1))),0),
   Return, IF(COLUMNS(XYLocations)-1,DROP(REDUCE("",SEQUENCE(ROWS(XYLocations)),LAMBDA(p,q,HSTACK(p,TAKE(DROP(returnGrid,(INDEX(XYLocations,q,1)-1)*IF(OutputArray,stepRows,1),(INDEX(XYLocations,q,2)-1)*IF(OutputArray,stepCols,1)),OutRows,OutCols)))),,1),NFout),
   IF(ISOMITTED(returnGrid),
      FindLocations,
      IF(Return<>0,Return,""))
   ))

so the idea is that it can take a [pattern] to match in a [lookUpGrid] and then if [returnGrid] is given it will return the corresponding location in that grid. Note, if that [returnGrid] is NOT the same dimensions as the [lookUpGrid] it will return a single cell (e.g. brail dot grid (3x2) can lookup on brail dot grid array (3i x 2j) and return from a corresponding table of letters/characters (i x j) ). Also if [returnGrid] is not provided a grid of 0/1 will be returned corresponding to the location(s) where the search pattern was found.  NOTE: this will return ALL matches for the pattern (arranged in a stacked row).  Another feature is the [step_rows] and [step_cols] so you can tell the search to step by a specific amount.  So in the example where there is a row of characters between each line you can use a [step_rows] of 4 to skip over those spacer lines.  You can also force a [step] of 1 to 'scan' all possible combinations of the search array for that pattern.  I've tested a few different combinations and it seems pretty robust.  Let me know what you think.

BTW. on line 12 I am just creating a list of all possible x,y coordinates and tried using the trick i leaned below to use IF( c, QUOTIENT, MOD) (value1, value2) but I kept getting a memory error because I was trying to create a 2d array using c => {1,0}.  It worked fine when I declared them fully inside the IF but doing that trick to pass them as functions didn't work in this array version.

And although it isn't a specific optional output, the XYLocations function returns a list of where the pattern is found in a 2 column row-col table of values.

Here is an example of using it to find the first character (S) from the Answer key.  I rewrote the letters in a single spaced grid.  But if you take the overlapping grids (so the [returnGrid] is B3:Q18) you would still get S out but also with the other 5 cells of the offset 3x2 grid as shown just to the right of the highlighted cell (C25:D27).