Understanding the Array form of the Excel INDEX() function


Can someone provide some insight and example(s) for using the Array form of the INDEX() function in Excel?

The following screenshot is from an Excel file (attached) which I've been using to play around with different forms of the INDEX() function. I think I deciphered the Reference form of the function (in a different sheet tab in the attachment) but the Array form is still murky.

In the above screenshot, the range shaded pink (C17:I34) is named AreaC and I've been testing formulas in the two cells shaded yellow. I do understand that Array formulas must be entered using CTRL+SHIFT+ENTER but I discovered that you cannot show the curly brackets AND the nice colors in the Formula Bar simultaneously so the former are shown in a second screenshot below.

I'd appreciate it if someone could use the range in the attached file to provide an example or two of how to correctly set up an Array form of INDEX() and briefly explain when/why you would use it.


File attached below

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


You seem to be getting is pretty well from what I can see.

The first one (Cell E12) has a #REF since you are referring to an array (AreaC) with more than one column, but you have not told it which column to pick from.

If you change 'AreaC' to be, say, I17:I34 then it would work and give you 'p' (Cell I22) being the sixth element in that array.

Why would you use this?

Example:  If you had a table of interest rates on loans, and you had 'term' on one axis and 'borrower's credit rating' on the other, then you could use INDEX to find the interest rate for, say, 3 years for someone with a credit rating of 'D'.

Hope that helps,


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
abbas abdullaCommented:
There are two approaches for your formula to work properly :

1. Use match function to find the row and column number. Formula =index(AreaC,match(C12,B17:B34,0),match(D12,C16:I16,0))

2. Simpler formula , your formula will become:
Ejgil HedegaardCommented:
Index is per design an array formula, and shall not be entered with Ctrl+Shift+Enter, unless some of the arguments require that.
But generally not.

The array form is the simplest, and the one you would typically use, with one array (range), where the row and column arguments point to a specific cell in the range.
The result is a single value.

Or if row or column is not used, like INDEX(Range,Row, ) or INDEX(Range,  ,Column), returns all values in the row or column.
The result is a range (array) to use as argument for another formula that use ranges as argument, like SUM or COUNTIF.
Used alone it will only show the first value in the range.

The reference form is in principle the same, but instead of one range, there can be more ranges, and the Area number argument tells which range to use.
It is the same as defining the range to use by some IF statements, like
IF(AreaNumber=1, INDEX(AreaA,Row,Column), IF(AreaNumber=2, INDEX(AreaB,Row,Column), INDEX(AreaC,Row,Column)))
With the reference form, IF and INDEX are combined to make a shorter formula
WeThotUWasAToadAuthor Commented:
Thanks for the comments and explanations.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.