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

LIMITED TIME OFFER
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 formulas concatenatevb conditional statementsinsert macro in excelsql where operatorsvba file namevba checkbox valuevba workbook activateexcel join cellsfilecopy vbainsert drop down box in excel 2007excel macro loopsexcel project management dashboardproject management excel spreadsheetexcel vba transposemacro delete rows based on cell valueexcel if statement examples textcopy to clipboard vbadelete function in excelexcel unprotect sheet forgot passwordexcel merge sheets by columnhow to alphabetize in excel 2003activex combo boxvba create worksheetexcel vba font colorhow to write a macro in excel 2007vba activeworksheetlearn to code vbavbscript for excel tutorialexcel sheet shortcutsmacros codingsql dml ddlms access run query vbavba datesexcel macro case statementhide worksheetunhide rows excelloops excelvba rename filesvb sample programs for beginnerscheckboxes in excelexcel column width in inchesexcel vba change cell color based on valuefor each vba excelinterview question answer for fresherexcel vba absolute valueexcel consolidate columnsadvanced excel tutorial pdfand condition in vbaformula of vlookup in excel with examplerange property vbausing iserror with vlookupconditional statements in excelunprotect workbook excel 2007remove dupes in excelvba code to save workbookdefine workbook and worksheetexample of a macro in excelvlookup examples in excelword vba programmingdefinition of vlookupvb calculator source codeexcel and macroshow to protect excel from editingvba addressexcel vba file openexcel macro current rowvba select workbookhow to merge data from different excel filesaddition in columns worksheetsvbscript excelhow to create hyperlink in excelexcel shortcut charthow to build a macro in excelvba output to text fileadd the developer tab to the ribbon wordcreating pivot table in excel 2013excel application getopenfilename