Xlookup Question

[Tommy2355]

I've used Vlookup for years to merge data to reps names to review their performance. At the first of the month when not all stores have been seen, Vlookup returned a #N/A when there is nothing to match. I simply use Ctrl H to replace that with a 0. Quick and simple. Xlookup looked good cause I can request a 0 to be added when there is not a match. When I bring over data into empty cells the process works perfect. Then to do an update into the same cells where there is already data form the first week, I get a SPILL return and it doesn't work. I moved one column over and tried the same thing and it populated the empty column just fine.  I then went back to the original column with data in it and got the same response, SPILL. Does Xlookup only work in empty cells?

[#2]

It would help if you included the actual formula or better yet a sample sheet. That said, it sounds like your XLOOKUP is trying to return an array (hence the SPILL). please note that the format of XLOOKUP and VLOOKUP has changed more than just adding the extra options at the end. In particular VLOOKUP is value, 2-d array, index while XLOOKUP is value, 1-d array, 1-d array

[#3]

Thanks, first post in a long time and forgot to add any actual formula. After viewing several video, I realized that Xlookup needs clear fields to populate data. AFter I clears out the fields under the cell where the SPILL was, the column populated quickly. After using XLOOKUP several times, I do see a little advantage over VLOOKUP but not really a lot. Will just bounce back and forth to maximize my time based on what has to be done.
Thanks for the feedback