INDEX Function in Excel – Formula Explained with Examples

Home/Excel Formulas/INDEX Function in Excel – Formula Explained with Examples

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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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

 
 
Related Resource External VBA Reference
Excel VBA Reference Project Management Reference
VBA Reference:
Excel Reference:
By |February 21st, 2013|Excel Formulas|0 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

Leave A Comment


Related pages


excel duplicate rowexcel vba worksheet namemerge 2 columns in excel 2010vb6 backcolordelete entire row vbaexcel macro writinghow to protect sheet in excelunprotect xls filevba cells rangepivot table auto refreshexcel macro hide columnhiding a cell in excelhow do you merge and center cells in excelhow to use sumif in excelnested if statements excelprotected excel sheetexcel vba case statementvba macro wordsample meeting minutes template wordexcel macro if cell containsexcel executive dashboardhow to copy a worksheet in excelvba move fileshow to connect excel to sql servereasy vlookup examplehow do you delete a sheet in excelselect sheet vbasql query interview questions and answers for freshers pdfname manager excel 2007excel vba xldownchange the chart type to clustered columncoding vbastatic vbavba autofilter datehow to create vlookup in excel 2007declaring arrays in vbahow to remove excel formulaadvance excel tipscolor index excelexcel format hyperlinkvba autofitexcel create macro buttonunhide a column in excelexcel macro close without savingwhat is ddl commanddim conn as new adodb connectionexcel line chart templateshow to access vba in excel 2007vba until loopexcel vlookup syntaxcombobox multiselectwhat is a userform in excelexcel vba set activecellrunning a macro in excelhow do you password protect an excel spreadsheethow to check duplicates in excelhlook up excelfso vbamacro excel insert rowexcel vba tutorconstruction cost estimator excelms excel substitutevba listbox columnmonthly schedule template excelexcel macro useslearn vba for excelhow to build excel macrosvb clear listboxdim vbaworkbook excel vbahow do you sort columns in excel