Forum Discussion
Solved
Array and Range
Hello What are the differences between Range and Array in formulation?
Without a test workbook I cannot be sure but I would suggest moving the formula to a Defined Name e.g. 'underManned?'. Some aspects of formula evaluation are not as good as they could be within Conditional Formatting.
Other observations are that the coercion from TRUE/FALSE to 1/0 will take place when you multiply conditions so the '--' are surplus to requirements, and that a formula evaluated within Name Manager is always an array calculation (like 365) so SUM will work as well as SUMPRODUCT in that context.
This way the formula used for the conditional formatting is
= underManned?
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.