INDEX

INDEX(reference, row_num [,column_num] [,area_num])

Array Form - INDEX(array, row_num, column_num)

Returns the value from a cell range which is the intersection of a row AND a column.

reference	The reference to one or more cell ranges.
array	(Array Form Only) The array of values to use.
row_num	The row in array from which to return a value.
column_num	(Optional) The column in array from which to return a value.
area_num	(Optional) The area to use when there is more than one.

REMARKS

* This function can return multiple values.
* For an illustrated example refer to the page under Lookup Functions
* When this function returns multiple values it will create a Dynamic Formula in Excel 2021.
* This function will return the value in the cell (or array) at the intersection of the "row_num" and "column_num".
* The array form can be used in conjunction with an array constant but is not used very often.
* If "reference" contains non adjacent cells, then an "area_num" must be provided.
* If "reference" contains non adjacent cells, the cell ranges must be enclosed in parentheses.
* If "row_num" is left blank, then "column_num" is required.
* If "row_num" is too small or too large, then #REF! is returned.
* If "column_num" is left blank, then "row_num" is required.
* If "column_num" is too small or too large, then #REF! is returned.
* If "area_num" is left blank, then 1 is used.
* If "area_num" is too small or too large, then #REF! is returned.
* You can use the CHOOSE function to return the value in a row (or column) based on an index number.
* You can use the XLOOKUP function to return a value in the same row (or column).
* You can use the XMATCH function to return the position of a value.
* You can use the MATCH function to return the position of a value.
* You can use the VLOOKUP function to return a value in the same row.
* You can use the HLOOKUP function to return a value in the same column.
* For the Microsoft documentation refer to support.microsoft.com

	A	B	C
1	=INDEX({1,2;3,4},2,2) = 4	Apples	40
2	=INDEX({1,2;3,4},0,2) = 2	Lemons	20
3	=INDEX(B1:B2,1,1) = Apples	Bananas	30
4	=INDEX(B1:B2,2,1) = Lemons	Peaches	60
5	=INDEX((B1:B2,C3:C4),1,1,1) = Apples
6	=INDEX(B1:C4,MATCH("Bananas",B1:B4,1),2) = 40
7	=INDEX(B1:C1,,2) = 40
8	=INDEX(B1:B4,,1) = #VALUE!
9	=INDEX(B1:B4,,2) = #REF!