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


thisworkbook vbavba sortingpm schedule excel templatewhat are the shortcut keys in ms exceluserform vba excelsurface chart in excelportfolio management dashboard exceluse of vlookup and hlookupaptitude test questions and answers for freshers pdflocate duplicates in excelexcel project management templatesvba excel variableexcel populate comboboxsql dcl commandscountif function in excel examplepivot a table in excelxml generator from exceladodb recordset vba excelautofit in exceldelete duplicates in excel columnuserform combobox vbaexcel formulas valueexcel lookup examplesexcel template for tracking tasksddl queryexcel password recovery macroexcel hide sheetsample excel macro programsclear comboboxunlocking excel workbookexcel vba code samplesdelete a row vbavlookup functionexcel aptitude questionsexcel yearly plannerhow to record a macro in excel 2007vba integer data typeexcel find duplicate values in columnexcel vba loop untilvba for excel pdfconsolidate excel worksheetsexcel vba copy data to another workbookauto filter definitioncbo boxofficeexport excel macrosiserror functionvlookup shortcuthow to password protect excel file 2010sas macro programming examplesinterview questions answers for freshersunprotect protected excel sheetlogical formulas in excel 2007vba combobox valueactivesheet listobjects addunprotecting exceldouble vbatelecommunications interview questions with answers pdfmsgbox iconsuse of concatenate in excelquery sql database from excelvb calculatorhow to merge cells excel 2013how to protect columns in excel 2007 with passwordvisual basic listboxwhere do i find a tick in excelvba practice projectsvba xlkpi gauge excellogical statements in excelloop vba codevb msgboxexcel vba activate workbook