XLOOKUP Doesn’t Always Spill

But it can

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

=XLOOKUP(F2,A2:A7,B2:D7,{"","",""})

This forces the #SPILL! error.

 

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.