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.

referenceThe reference to one or more cell ranges.
array(Array Form Only) The array of values to use.
row_numThe 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

* For an illustrated example refer to the page under Lookup Functions.
* 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.
* This function can return a single value or multiple values.
* This function can return multiple values when entered as an Array Formula using (Ctrl + Shift + Enter).
* 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 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.office.com

 ABC
1=INDEX({1,2;3,4},2,2) = 4Apples40
2=INDEX({1,2;3,4},0,2) = 2Lemons20
3=INDEX(B1:B2,1,1) = ApplesBananas30
4=INDEX(B1:B2,2,1) = LemonsPeaches60
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!  


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited Top