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.

referenceThe reference to one or more cell ranges.
row_numThe row in array from which to return a value (can be left blank).
column_numThe column in array from which to return a value (can be left blank).
area_num(Optional) The area to use when there is more than one.
array(Array Form Only) The array of values to use.

REMARKS
* For an illustrated example refer to the page under Lookup Functions
* This function can return multiple values.
* This function can create a Dynamic Array Formula.
* This function will return the value in the cell (or array) at the intersection of the "row_num" and "column_num".
* If "row_num" is left blank, then 0 is used.
* If "row_num" = 0, then all the values in the "column_num" are returned.
* If "row_num" is too small or too large, then #REF! is returned.
* The "column_num" argument is not really optional because you must include the comma. See Example 6.
* If "column_num" is left blank, then 0 is used.
* If "column_num" = 0, then all the values in the "row_num" are returned.
* 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.
* If "reference" contains non adjacent cells, the cell ranges must be enclosed in parentheses.
* 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 VLOOKUP function to return a value in the same row.
* You can use the HLOOKUP function to return a value in the same column.
* You can use the LOOKUP function to return a value in a row (or column) that matches a value in a column (or row).
* You can use the MATCH function to return the position of a value.
* You can use the CHOOSE function to return the value in a row (or column) based on an index number.
* You can use the INDEX and MATCH combination to lookup a value in any column.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

 ABC
1=INDEX(B1:C3, 1, 1) = BearingsBearings8
2=INDEX(B1:C3, 1, 2) = 8Bolts11
3=INDEX(B1:B3, 2, 1) = BoltsWheels6
4=INDEX(C1:C3, 2, 1) = 11  
5=INDEX(C1:C3, 3, 1) = 6  
6=INDEX(B1:C3,1,) = { Bearings, 8 }  
7=INDEX(B1:C3,0,2) = { 8; 11; 6 }  
8=INDEX(C1:C3, MATCH("Bolts", B1:B3, 0)) = 11  

1 - What value is the intersection of row 1, column 1 in the range "B1:C3".
2 - What value is the intersection of row 1, column 2 in the range "B1:C3".
3 - What value is the intersection of row 2, column 1 in the range "C1:C3".
4 - What value is the intersection of row 2, column 1 in the range "C1:C3".
5 - What value is the intersection of row 3, column 1 in the range "C1:C3".
6 - What value is the intersection of row 1, with a blank column number in the range "B1:C3". This returns 2 values.
7 - What value is the intersection of row 0, column 2 in the range "B1:C3". All the rows are returned from column 2. This returns 3 values.
8 - What value in column "C" is on the same row as "Bolts" in column "B".

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