Note: in Excel 365, you will can see lifting happen in real-time, since multiple results will spill onto the worksheet. In earlier versions, lifting still occurs, but only one result is displayed in the cell that contains the formula.
Example
The example shown illustrates what happens if you call the LEN function on the range C5:C7, which contains three values. LEN isn’t programmed to handle arrays natively, so LEN is run three times, once for each value in an operation like this: Notice the result is a vertical array with three values, just like the source range.
Dealing with multiple results
When lifting occurs in a formula, there will be multiple results, and these need to be catered for. In the example above, because LEN returns three separate values in an array, we need handle the output with a function that can work with arrays. One option is to calculate a total character count in the range C5:C7 using SUMPRODUCT: SUMPRODUCT will handle arrays natively, so this formula does not require control + shift + enter. The SUM function could be used as well, but would need to be entered with CSE: Note: A special case of lifting is called “pairwise lifting”, which is combining arrays in a pairwise fashion. Author
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
