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:

INDEX Function in Excel - Snt

Syntax 1 – Returning a value:

INDEX( array, row_number,

[column_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

INDEX Function in Excel - Example 1

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

INDEX - Example 2

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