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


how do you unhide columns in excel 2013vbcrlf vbahow to remove blank row in excelbubble chart excel templateinsert several rows in excelvb6 with access database exampleexcel macros vba examplesinsert a column in excelexcel formula functions and examplesmicrosoft excel autofitexcel quick tutorialunhide excel worksheetexcel vba call subhlookup vs vlookuphow to insert a dropdown in excelhow to delete hyperlink in exceldrop down list in excel 2007integer function excelexcel adopower vbams project plan templates freevba in excel 2010 tutorial pdfapplication calculation xlcalculationautomaticgantt chart template powerpoint free downloadexcel vba strexcel vbssql sortingcreate sheet vbavba codes examplesupdate screen vbaconcatenate excel 2013password protect excel macroexcel activex comboboxbreak loop in vbahow to unhide columns in excel 2007pm templates for excelrename worksheet vbausing the match function in excelhow to use macros in excel 2003form vbause of vlookup in excel 2010ms excel lookup function examplemacro example in excelexcel formula protectionconvert excel column width to inchestrim excel formulasql vba accesssas interview questions on macroswhat is meant by vlookupvba option base 1excel macro save as csvms access vba run sqlconcatenation in vbaexcel vba offsetexcel sumif formulavba applicationsexcel vba tricksexcel joining cellsexcel macro to select a cellclose excel workbook vbavba expertcase vba excelhow to use countif formula in excel 2007lookup in vbavba excel 2007 tutorialhow to unhide a row in excelwhat is a vba macrosas coding for beginnersexcel special characters in formulahide a worksheet in excelexcel formula to delete duplicatesexcel vba select a cellhow to create project timeline in exceleffort estimation templateexcel tips and shortcuts