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


loop through worksheets vbacombine multiple cells in excelcombobox controlchoose vbaexcel control commandsstep by step pivot table excel 2010excel vba adodb connection stringexcel chart vbahow to record a macro in excellearning sas by example data setsexcel find duplicate entrieschange the width of a column in excel 2010excel macro current rowcombining data from multiple worksheetscommand button vba excelconcatenate formula in exceldcl sqlexcel pivot table shortcut keyvba objectsaccess vba programming pdfvba active cellvba sort columnexcel unlock password protected sheethidden numbers worksheetexcel vba remove rowunprotect xls fileadd developer tab to excel 2007vba workbook activatetechnical questions for freshers interviewexcel vba lengthbypass excel passwordexcel unprotect sheet onlineexcel find last cellexcel 2007 protect sheetexcel macro change cell colorexcel vba color codesexcel 2010 slicer tutorialautofit excel columnsexcel vba formulavba adodb.connectionvba color codeslookup function in excel 2007delete unused cells in excelautofilter vba excelsimple excel test for interviewfor loop in macro excelexcel lookup formulaexcel vba columnchart wizard exceltest automation interview questions and answersvba inputbox cancelvba close fileunprotect excel onlinehow to set column width in excelhow to remove duplicate data in excel 2007how to delete empty cellsrept in excelvlookup in excel formula exampleexcel template checklistexcel countif exampleshow to merge worksheets in excelexcel pivot tutorialhow to hide tabs in excelunprotect excel worksheethow to unhide worksheetfree dashboard templates powerpointexcel 2010 remove blank rowsbeginner excelprotect an excel workbookvba autofitconstruction cost estimator excelexcel 2007 checkboxvba excel workbooks.openhow to unlock a locked excel filevba userform design