XLookup array formula will not fill down

[botoole54]

I used Xlookup to create an array formula to return 5 columns of data. The formula works fine, but I can't get it to fill down. If I change the Xlookup formula to return just 1 column of data, then I can double-click the bottom right corner, and the formula fills down all 44,000 rows.

[CK76]

Array formula, will auto expand to range, but formula is only held within first cell (top left). So, you need to select G2, then drag down. Not K2.

EDIT: Note you can't double click, but click on the bottom right of G2 and drag down.

[botoole54]

Hi, thanks for looking at this! I know that I can drag and/or copy the formula from G2, but most of my files have a lot of rows. Is there any way to auto-fill when I use the array formula?

[hrlngrv]

If you select F2:K2, you can double click on the fill handle in K2 to fill down. At least that worked for me just now in web Excel. I don't have access to O365 at the moment to test with that.

I figure this is just the new semantics of spilled formulas. If your formula spills across columns in a single row, you have to fill the entire spilled range rather than just work with the 1st cell in which you'd edit the formula. Maybe that changes over time, so it may be worth using the Feedback app to request that MSFT change this to allow intelligent filling when clicking on the leftmost cell of a spilled formula's range.

[CK76]

Autofill via double click is disabled on purpose. Since array result can span more than one row/column.
I'm afraid, you can only drag down.

If you break it down into component parts, you could. But then that defeats the purpose of using array

Alternate method.
Copy G2. Select F2 and CTRL + DOWN. Hit RIGHT. Then CTRL + SHIFT + UP. Paste.

[ChemistB]

I would do this from the name box (where you usually see the cell ID- upper left)
Click in the name box and write over what's there. Enter your range including the cell where your formula is
i.e. G2:G44210
Then CNTRL+D to fill down

[Gregb11]

In your formula in F2, you can change it to address the whole array so you only have the formula in one cell and it will SPILL down:

=XLOOKUP(D2:D11,Table3[Vendor Name],Table3[[Full Name]:[Full Name]],"not found",0)

[Document-Magic]

I have found a workaround for this. If you make the lookup value in the first row one that will return an #N/A, then you can double click to autofill down. That's weird. I don't know why you cannot double click to autofill down or why my workaround works. I have named it the Tanya Workaround in honour of the student in my Excel class who found it.