Forum Discussion
Array and Range
Hello
What are the differences between Range and Array in formulation?
A range is a collection of cells, and has a location on the sheet that is returned by the ROW function. An array is comprised of a set of numbers and does not return cell locations.
VLOOKUP returns numbers or arrays of numbers, whereas XLOOKUP returns range objects that may be combined using the intersection, union and composition operators, for example
XLOOKUP("b",C4:C8, C4:C8):XLOOKUP("d",C4:C8,D4:D8)returns a multi-cell range, whereas an equivalent expression using VLOOKUP is invalid.
It is easy to confuse the two because the default property of a range is its value so what you see as the output of a formula is based upon the array of values in the input range. Other range properties include its .formula, its .interior.color and many other things that make a worksheet look 'pretty'. Arrays have none of this, they are just numbers held in memory.
5 Replies
- Another example of a range reference versus array, say you are using xlookup to look some value up in column A and return column C:
=XLookup(value, A1:A10, C1:C10)
A1:A10 is a range reference.
Now, say you want to perform the lookup using 2 criteria (columns A and B):
=XLookup(1, (A1:10=x)*(B1:B10=y), C1:C10)
A1:A10=x returns an array of True/False values.
B1:B10=y returns an array of True/False values.
(A1:10=x)*(B1:B10=y) returns an array that is the result of multiplying the first two arrays (True=1 and False=0).
You often see arrays being created by some conditional comparison or mathematical operation being performed on a range - the intermediate results of the operations don't exist on a worksheet, but are stored in memory (a point that has already been mentioned). A range is a collection of cells, and has a location on the sheet that is returned by the ROW function. An array is comprised of a set of numbers and does not return cell locations.
VLOOKUP returns numbers or arrays of numbers, whereas XLOOKUP returns range objects that may be combined using the intersection, union and composition operators, for example
XLOOKUP("b",C4:C8, C4:C8):XLOOKUP("d",C4:C8,D4:D8)returns a multi-cell range, whereas an equivalent expression using VLOOKUP is invalid.
It is easy to confuse the two because the default property of a range is its value so what you see as the output of a formula is based upon the array of values in the input range. Other range properties include its .formula, its .interior.color and many other things that make a worksheet look 'pretty'. Arrays have none of this, they are just numbers held in memory.
In general array is set of objects which sits in the memory; range sits in the grid. For some functions range is automatically converted into array, e.g. SUMPRODUCT(). For some not. Array returned (spilled) to the grid as one object is spill, e.g. with FILTER().
- Thank you
If I want to have more details in this regard, is it possible to introduce a reference?Sorry, I don't know one. My guess if you google something like "Excel range vs array" it shall be lot of posts.
