1

The answers to this Excel question and links to other pages were very helpful understanding arrays in Excel.

I played around with it for a while and did not detect a general rule that covered all the situations. I continue to be a little puzzled about the behavior of the INDEX() function.

Here is a workbook that demonstrates some of the things I discovered. They mostly have to do with "edge cases" like a one-dimensional array or range when using the row and/or column arguments. If the notes in the workbook are not clear, please comment here.

Question: If someone else could come up with a general explanation of why INDEX() behaves as it does, that would be great, especially if it also generalized to other array formulas and functions.

Thanks!

Improve this question

1 Answer 1

Reset to default
1

Your example sheet is very thorough.

N/A Errors: When you use array functions (what Excel calls functions spread over a cell range and in the curly braces { .. } ) and the function returns a smaller grid of results than the range the function is asked to fill through the array function , it puts #N/A for Not Available Error in the surplus cells.

REF Errors: When the supplied range to the INDEX(..) function is too small for the row and column index arguments you get a #REF error. The #REF error is Excel's way of telling you that it can not find the cell you are trying to reference. #REF is short for Reference Error

TRANSPOSE Issue: You have REF errors in Column D because you have asked for the third column's value (_x3) in a 5x1 shaped range (the transpose of the 1x5 shaped range at A48:E48 is 5x1 shaped). Likewise Column C returns the value 5. Rows 54 and 56 have the same underlying issue.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I think my workbook was a little unclear about which things I was questioning. I knew that my array bounds were larger than the arrays in some cases, and I understand most of the N/A and REF errors. I'll update the sheet tomorrow to clarify what I'm asking about. Mostly, I don't understand the flakiness of INDEX() in its use of the row and column arguments. Just one example: you mentioned Column D in my example, and Column D shows what I expected, but why does Row C above that not also have REF errors? (See the note to the left of Row C).
In Column D you skip an argument putting the 3 in the 'col' position =INDEX(TRANSPOSE(A48:E48),,3) by omitting the row argument you invoke INDEX in it's array mode and get all the rows at column three, but you only have one so it repeats. In Column C you have =INDEX(TRANSPOSE(A48:E48),3) fixing the value for the 'row' and allowing =INDEX() to default the 'col' value in array mode, but col 3 does not exist at all.
Sorry I did not have time to update the workbook today. Regarding your comment on Columns C and D, those behaved as I expected. My puzzlement had to do with Rows C and D in the grid above the column examples. I expected Row C to show #REF! errors the same way Column D did, but it didn't. I discovered that =INDEX(A48:E48,3,) (note trailing comma) does cause the #REF! error. (Also note that all the formulas were entered as array formulas with CSE.) I'm keeping notes on all this and hope I can come up with a comprehensive description of how this function works.
I applaud your efforts to document the INDEX function however there is no standard necessarily enforced between Excel functions. They can and probably do treat these edge cases differently. I know from experience writing Excel functions using their C /xll API that it is left up to the programmer's judgement what to do.
I'm glad to hear about your experience. I have not used an Excel API, so it helps to know that. I will continue to explore the INDEX () function so I can become more comfortable with it. Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.