1

So I'm having difficulties understanding fully how arrays works and when they are used by excel and specifically what happens in the background.

  1. From reading the past few hours I understand that one of the reasons my Index Match doesn't work without array is simply because its a multicriteria Match that I use as below:

    {=INDEX(D30:E36,MATCH(F33&G33,B30:B36&C30:C36),2)}

    From what I understand the reason is that Match returns a {x,y} result which classifies it as an array formula. But considering the point is to get a row number, if the row I'm looking for is 5 then Match will return a {5,5} for row number for Index. And then Index interprets this as just 5? or what exactly happens in the background here?

  2. Then I found an article which showed how to circumvent the array formula and not need ctrl+shift+enter as shown below. How does the below change things and what happens in the background?

    =INDEX(D30:E36,MATCH(F33&G33,INDEX(B30:B36&C30:C36)),2)

  3. The below is a an array SUM/COUNTIF formula which counts unique cells only which does not work without array brackets. Why is that and how does it work? It involves maths so I'm not sure.

    {=SUM(1/(COUNTIF(A1:A5,A1:A5)))}

Thank you!

Tony Georgiev
  • 23
  • 2
  • *From what I understand the reason is that Match returns a {x,y} result which classifies it as an array formula.* In this case, it doesn't. It will return a single result. Your `MATCH` is searching for the *single* value of `F33&G33`, which is not an *array*, but rather the *concatenation* of two values. E.g. if `F33` and `G33` are `2` and `5`, then `F33&G33` is `25`, but `F33:G33` is `{2,5}` (which is an array). – ImaginaryHuman072889 Dec 14 '17 at 12:52
  • Re: question 3 - it's not quite the same but see my answer here and some explanation: https://stackoverflow.com/questions/18586815/count-unique-values-in-a-column-in-excel/18588144#18588144 – barry houdini Dec 14 '17 at 13:32
  • For general info on array formulas see here - http://www.cpearson.com/excel/arrayformulas.aspx – barry houdini Dec 14 '17 at 13:35
  • F9 higlighted parts of the formulas to see the array objects they create, that helps to understand waht is happening behind the scenes – Glitch_Doctor Dec 18 '17 at 11:36

0 Answers0