Understanding the Array form of the Excel INDEX() function

WeThotUWasAToad
WeThotUWasAToad used Ask the Experts™
on
Hello,

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.

2017-10-27a.png
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.

Thanks

File attached below

2017-10-27b.pngINDEX---function-in-Excel-2017-10-2.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Consultant
Commented:
Hi,

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,

Alan.
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:
=index(AreaC,C12,D12)
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
INDEX((AreaA,AreaB,AreaC),Row,Column,AreaNumber)

Author

Commented:
Thanks for the comments and explanations.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial