Make the spilled range operator (#) return the array returned by a cell, even if the array cannot spill (#SPILL! error)
The spilled range operator (#) is great for being able to reference the entire dynamic array, no matter what its dimensions are. However, if the dynamic array cannot spill (i.e. it returns a #SPILL! error), then any reference to that array will also be a #SPILL! error.
In many cases, a user might want to work with an array returned by a cell even if it cannot spill. For example, people that deal with many/large matrices probably do not want to have to ensure that each matrix has enough room to spill before it can be used in another calculation. If the FILTER function is used in multiple columns of a table and each call to FILTER uses some of the same conditions, it would be useful to store these common conditions (arrays of TRUE/FALSE) in a helper column. This would result in faster, less repetitive formulas.
Please change the behaviour of the spilled range operator to the following: If the array has spilled, return a reference to the spilled range (i.e. keep the current behaviour). This allows for operations such as counts of rows or columns. However, if the array cannot spill (#SPILL! error), return the array that failed to spill.
Kenneth Barber commented
Angela, the 1st argument of VLOOKUP is supposed to be a single value or a reference to a single cell. You have a reference to a whole column in there.
Also, I would prefer if your comment was relevant to the suggestion that I posted. UserVoice is for suggestions, not questions. Lastly, I don't think that the company that you work for would appreciate you sharing their confidential information like that.
Doing V Lookup but getting spill error. What do I need to do?