What is the use of INDEX function?
INDEX Function in Excel returns either the value or the reference to a value from a table or range.
What is the syntax of INDEX function?
It has two syntaxes:
Syntax 1 – Returning a value:
INDEX( array, row_number,
array: The range or a range name containing the table of data.
row_number: row number in the array to be used to return the value.
column_number: column number in the array to be used to return the value.
INDEX Function in Excel – Examples – Returning a value
Example 1: Returns Row 1 and Column 2 Value =i.e; DW
=INDEX(A4:C7,1,2) returns the first (1) value from the second (2) column in the range (A4:C7). i.e; “DW”
Example 2: Returns Row 1 and Column 1 Value i.e; 1008
=INDEX(A4:C7,2,1) returns the second (2) value from the first (1) column in the range (A4:C7). i.e;1008
Example 3: Returns Row 4 and Column 3 Value i.e; 50
=INDEX(A4:C7,4,3) returns the fourth (4) value from the third (3) column in the range (A4:C7). i.e;50
Example 4: Returns Row 5 Value i.e;1025 (Note: Here Range contains only one column, no need to mention the Column number)
=INDEX(A4:A7,4) returns the fourth (4) value from the range (A4:A7). i.e;1025
Syntax 2 – Returning a Reference to a Value:
INDEX( reference, row_number, [column_number], [area_number] )
reference: reference to a range or group of ranges.
row_number: row number in the range to be used to return the reference.
column_number: column number in the range to be used to return the reference.
area_number: range to use from the reference parameter. If this parameter is omitted, by default it consider the first range.
Examples on INDEX function – Returning a Reference to a Value
Example 1: Returns Row 1 and Column 2 Value =i.e; DW
Example 2: Returns Row 1 and Column 2 Value from the Second Area/Range =i.e; 700K
Example 3: Returns Row 2 and Column 2 Value from the First Area/Range =i.e; Admin
Example 4: Returns Row 4 and Column 1 Value from the Second Area/Range =i.e; 50
Reference:
Please refer the below article for more Lookup & Reference Excel functions.
Lookup & Reference Excel Formulas
Please refer the below article for more Excel Functions.
Excel Formulas | Home
