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.

kittenmeants since you are dealing with a spreadsheet that contains thousands of rows of data with merged cells, your best option is to first clean the data using VBA....

WARNING: the following proposed solution uses VBA to modify the data in place. It is highly recommended to back up your workbook before proceeding. Please read the entire solution carefully, including all supplementary notes and important information, to determine if it's right for you. Proceed at your own risk.

(1) Open the Visual Basic editor by pressing Alt+F11 on your keyboard. If that doesn't work, it can also be opened from the Developer Tab on the ribbon. If the Developer Tab is not visible, go to File > Options > Customize Ribbon, check the box for Developer (under Customize the Ribbon > Main Tabs) and click OK.

(2) In the Visual Basic editor, go to Insert > Module and paste the following code:

Option Explicit

Sub CleanData()

'Clear filters, if necessary
    Dim ws As Worksheet
    Set ws = ActiveSheet
    If ws.FilterMode Then ws.ShowAllData

'Unmerge all cells in the target range
    Dim rg As Range
    Set rg = ws.Range("A1").CurrentRegion
    Set rg = rg.Offset(1).Resize(rg.Rows.Count - 1, 4)
    rg.Cells.UnMerge

'Load the target range into an array and fill values down
    Dim data As Variant, i As Long, j As Long
    data = rg.Value
    On Error Resume Next
    For j = LBound(data, 2) To UBound(data, 2)
        For i = LBound(data, 1) To UBound(data, 1)
            If data(i, j) = vbNullString Then data(i, j) = data(i - 1, j)
        Next i
    Next j

'Write the cleaned data back to the target range
    rg.Value = data

End Sub

(3) To run the code, go back to the worksheet, press Alt+F8 on your keyboard (or go to the Developer Tab > Macros) and click Run.

NOTE: this code assumes there are NO blank rows within the data range, and the target range to be cleaned is only the first 4 columns. Furthermore, if sub-header or sub-total rows exist within the data range, the code would need to be modified to exclude those rows.

IMPORTANT: It's always a good idea to test any new VBA code on a COPY of your workbook, in case anything should go wrong. Without seeing your entire workbook, I cannot guarantee that the code will work 100% as intended.

kittenmeants 

Whether or not a viable solution could be found that could make your XLOOKUP function work, you really should be talking with whoever "owns" that table itself. It's a very badly designed table--and will continue to give you and others problems if you try to use it in its current state.

Quite seriously, it's designed for humans to look at rather than for computers to use, for Excel (and other programs to make use of). It's a common mistake, but a real one, because--as you're finding out first hand--it interferes with Excel's excellent abilities to retrieve data from tables. It's not bad to create spreadsheets that look nice, but that effort should be saved for output sheets, not raw data at the input end of things.