Merged Cells with XLOOKUP Help

kittenmeants 

I am late to the party and not absolutely sure what is sought.  However, if every row of a merged cell is to be treated as if it contains the value then some form of fill-down is needed.  Such problems arise when the needs of presentation are placed ahead of the need for further calculation.  The standard way of dealing with this using worksheet formula is now to use SCAN.  If one defines the LAMBDA function FILLDOWNλ to be

= LAMBDA(column,
    SCAN("", column, LAMBDA(a, v, IF(v <> "", v, a)))
  )

then one could either use

= FILTER(Associate, FILLDOWNλ(Store)="Gastonia NC")

to return a required list of associates or, for contiguous ranges, one can use a pair of XLOOKUPs.

= LET(
    first, XLOOKUP("Gastonia NC", FILLDOWNλ(Store), Associate, "N/A", 0, 1),
    last,  XLOOKUP("Gastonia NC", FILLDOWNλ(Store), Associate, "N/A", 0,-1),
    first:last
  )

The latter formula relies upon the fact that XLOOKUP returns cell references rather than simple values.