The new XLOOKUP function has the ability to spill when you select multiple columns to extract. Even when you do, it doesn’t always spill across.
In the image below you can see XLOOKUP returning three columns by spilling across.
If you delete the entry in cell F2 it blanks out the XLOOKUP and it doesn’t spill – see image below.
We can see it doesn’t spill because we can make an entry in the spill range and the #SPILL! error is not returned.
If we make a valid entry in cell F2 the #SPILL! error appears.
If you omit the error handler argument the #N/A error is only in the single cell as well – see below.
You can force XLOOKUP to spill by using array syntax. See the formula in the Formula Bar below.
The formula is
This forces the #SPILL! error.