Excel: What is the rule for when an array in brackets doesn't need to be entered as an array?

by

Such as this formula from “voyager”:

=LOOKUP(D7,{0,5,10,30,50,1}, {0,0.01,0.02,0.05,0.1,0.2})

It worked perfectly when NOT entered as an array using CONTROL+SHIFT+ENTER.

3

✅ Answers

🥇 Favorite Answer

  • You don’t include which version of Excel you’re using, and yes that makes a difference.

    Try asking the Excel experts and MVP’s in the Microsoft forum:

    Source(s): http://answers.microsoft.com/en-us/office/forum/ex…

  • Dim MyArray(3) MyArray(a million) = a million MyArray(2) = “b” MyArray(3) = 3 ~Edit~ in case you’re taking the line-via-line innovations-set then you somewhat can initialise an array of length n via utilising a loop. it somewhat is obtainable contained in here case: VBA for-loop MyArray(i) = Cells(x, j).fee //From Excel cells -or- MyArray(i) = AnotherArray(i) //to repeat from yet another array end Loop ~Edit~ (?) Tester5.ShellSize = Array(a million, 2) Am uncertain whether you have been hoping to initialise the winning array Tester5.ShellSize utilising Array(a million,2)?

  • This particular function doesn’t need to be CSE’d because you include the array in the formula, which is the lookup vector and result vector in curly brackets.

    With a LOOKUP, the “array” part is required in the formula itself, so it’s not considered an array formula like =SUM(IF(A1:A10=”Greg”, B1:B10)), which does require CSE. (of course, in 27 or later, instead of SUM(IF… you can just use SUMIF, which is not an array.)

    • Leave a Comment