Friday, 24 January 2020

worksheet function - Array formula does not return array (Excel 2016)


IMAGE


I am trying to use an Offset function to return numbers from a table (multiple rows and columns). However, the array is only returning the first cell.


This happens for all array formulas as well (such as TRANSPOSE), where dragging the formula down would not help transpose the data.



Answer




where dragging the formula down would not help transpose the data



Yep, entering formula in one cell and copying / dragging won't work.


These array formulas work correctly only if you enter them once: select all destination cells, type your formula and press CTRL+SHIFT+ENTER.


See also help on TRANSPOSE: https://support.office.com/en-us/article/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027


No comments:

Post a Comment

How can I VLOOKUP in multiple Excel documents?

I am trying to VLOOKUP reference data with around 400 seperate Excel files. Is it possible to do this in a quick way rather than doing it m...