Indirect with array of ref_text? I think it is possible!

[milatr98]

Hi,

I would like to start a discussion about the Indirect function with an array because I don't see much information about it when searching on the Internet.

First, we all know that the formula =INDIRECT("A1") will point to cell A1 and return the value on that cell.

Then, when I write it in array like =INDIRECT({"A1";"A2"}) or =INDIRECT({"A1"}) the result will be #VALUE!

 I searched for information on the Internet and many people said that INDIRECT "does not work with array". But when accidentally wrapping the INDIRECT in the CELL function, it returned the value. That makes me curious whether INDIRECT works with an array or not.

An array of... objects?

Normally I work with an array of values. Although I don't have much knowledge of coding, I worked with JavaScript sometimes, so I can imagine there is a kind of array like [{obj1};{obj2}...{objn}].

I questioned myself, what a cell is made of? Then, looking at the CELL function, I have some assumptions:
My first assumption is, a cell is an object. Some attribute-value pairs could be: {"address":$A$1,"contents":1,"row_height":5,"col_width":8,...}. 

I also assume that by default there could be a variable/someothername that refers to the "contents" attribute and Excel will read that to show the value. But if cells are wrapped in an array, the whole object will be passed as a value which will throw an error. 

My 3rd assumption is that some functions work by "read-reference" rather than "read-value" (i can't think of any better name). For example, ROW function when executing will look at "row_num" attribute of the cell and return the value paired with that attribute. The same to COLUMN function. That explained why the functions below work:

I believe N function will look directly at the "contents" attribute when the input is a reference

The same goes to =OFFSET(A1,{0;1},0)

A little more input, where I see the differences between SUM, SUMPRODUCT, SUBTOTAL, and AGGREGATE, which for me is really interesting:

I hope you guys find this topic interesting too ;) Discuss and correct me if there is anything wrong.

[#2]

For Office365 you can use BYROW() function then use INDIRECT() inside it.

[#3]

@Harun24HR yes, but that could be another topic. If the "array" parameter is an array, it behaves the same with indirect + array. If you put a reference to cell/range in that parameter, it executes differently.