• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

5 Excel INDEX Function Secrets

You are here: Home / Excel Formulas / 5 Excel INDEX Function Secrets
5 Secret Features of the Excel INDEX Function
June 11, 2020 by Mynda Treacy

The Excel INDEX function is a treasure trove of functionality, but most of us only know one way to use it. In this post I want to expose some lesser known quirks and ways it can be used.


 
  1. If the reference/array is a single row, you can put the col_num in the row_num argument’s position
  2. INDEX can return a cell or range reference
  3. 0 or skipping the row/column argument will return the whole row/column
  4. INDEX can be used either side of the colon, space or comma operators
  5. INDEX can return non-contiguous areas

Let’s start by quickly revisiting the syntax for the INDEX function. It has two syntax options:

Excel INDEX function syntax

The first version takes an array of values, and the other takes a reference to a cell, or range of cells. You don’t choose which syntax to use, Excel will decide that based on the inputs.

The output can be a single value, an array of values or a reference to a cell or range of cells. The output will depend on the context of the formula. For example, whether the formula is entered in a single cell, nested inside another function, array entered etc.

Watch the Excel INDEX Function Video

Subscribe YouTube

Download the Excel INDEX Function Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

1. Use row_num for col_num when reference/array is a single row

The formula in cell C16 looks up cells C7:F7 i.e. a row and returns the value from column number 2.

use row_num for col_num

Remember the syntax for the INDEX function relevant to this example is:

=INDEX(array, row_num, [column_num])

And the formula above is:

=INDEX(C7:F7,2)

Notice the col_num argument is in the row_num argument’s position. When the reference is a single row, you can simply specify the column number in the row_num argument.

2. INDEX Function Can Return a Reference

The formula in cell C16 below looks up the value for West, which is in row 4 and Q2 which is in column 2:

Excel INDEX function return a reference

Note: Most of us would use INDEX with MATCH to return the row_num and column_num arguments, but I want to keep this simple for the next example.

Depending on the formula, the return value of INDEX may be a value, as we saw in the previous examples, or a cell or range reference. In cell C24 (image below) INDEX returns a cell reference, D9, and because I have dynamic arrays, the results spill to the cells below:

Excel INDEX Function return a value

Excel 2019 and Earlier Notes:

  1. If you have Excel 2019 or earlier, you first select 4 cells, then enter the formula by pressing CTRL+SHIFT+ENTER.
  2. If you entered the formula in Excel 2019 or earlier without pressing CTRL+SHIFT+ENTER, Excel would only show you the first result i.e. 830 because it wouldn’t have enough cells to display all the results returned by the formula.

If you wrap the above formula in SUM (in any version of Excel) it will add the 4 cells like so:

Excel INDEX Function wrap in SUM

We can see INDEX evaluates to a cell reference in the Evaluate Formula dialog box:

Excel INDEX Function Evaluate Formula

3. Return a Whole Column or Row

If you place a zero in or omit the row_num or column_num arguments, INDEX will return the whole column or row, respectively.

In the example below in cell C16 you can see the row_num argument is zero, and INDEX has spilled the results of the whole column (D6:D9) to the cells below:

Excel INDEX Function return a whole column or row

Similarly, in the formula below, the row_num argument has been omitted, and again INDEX returns the whole column, which is then summed to return the total, 3,239:

returns the whole column

The same rule applies with the column_num argument as you can see in the examples below:

Excel INDEX Function column_num example

Of course, zeros in both the row and column numbers or omitting these arguments altogether will return the whole range specified in the array/reference argument:

zeros in both the row and column numbers

4. Operators

In example 2 we saw INDEX return a cell reference. This ability makes it perfect for returning dynamic named ranges. And if you know me then you’ll probably have seen me use INDEX for this before.

Colon Range Operator

The INDEX function can sit either side of the colon operator like so:

colon range operator

When teamed up with MATCH (see below) it can return a dynamic range that can adapt to changes in the selected quarters in cells C15 and C16:

=SUM( INDEX(C6:F9,3, MATCH(C15,C5:F5,0) ) : INDEX(C6:F9,4, MATCH(C16,C5:F5,0) ) )

Comma Union Operator

INDEX can also be used either side of the union operator, the comma. In the example below it is used to return two quarters, which are then summed:

comma union operator

When MATCH replaces the hard-keyed column references, it becomes dynamic:

=SUM(INDEX(C6:F9,0,MATCH(C24,C5:F5,0)),INDEX(C6:F9,0,MATCH(C25,C5:F5,0)))

Space Intersect Operator

The intersect operator returns the value at the intersection of two ranges.

space intersect operator

Again, we can team INDEX with MATCH to make the formula dynamic:

=INDEX(C6:F9,MATCH(B34,B6:B9,0),0) INDEX(C6:F9,0,MATCH(C33,C5:F5,0))

5. Non-contiguous Ranges

The less commonly used area_num argument allows you to reference multiple non-contiguous ranges. The references to the ranges are wrapped in parentheses and separated by a comma. I’ve named the ranges Product1 (C6:F9) and Product2 (H6:K9):

Excel INDEX Function non-contiguous ranges

The areas are numbered in the order you enter them, so in this example, product 1 is the first area and product 2 is the second area.

Note: Cell B13 contains the area_num. I’ve formatted it with a custom number format so that the text, ‘Product’, prefixes the number. You can see the format in the dialog box image below:

using custom number format

Notes:

  1. The area ranges must be on the same sheet, otherwise INDEX will return the #VALUE! error.
  2. The area ranges don’t need to be the same size, however it they aren’t it will be tricky to choose the correct row or column as it’s expected the structure of the tables is the same.
5 Secret Features of the Excel INDEX Function

More Excel Formulas Posts

ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.


Category: Excel Formulas
Previous Post:dynamic data validation lists in userformsDynamic Data Validation Lists in Userforms
Next Post:Excel STOCKHISTORY FunctionExcel STOCKHISTORY Function

Reader Interactions

Comments

  1. Anil N

    October 2, 2021 at 11:50 pm

    This is much helpful, thanks!

    One question- Is there any way to fetch 10 or 20 rows in sequence in a column using the INDEX function? I am just trying to have a workaround for the QUERY functions of Google spreadsheets. Any insights on this would be helpful.

    Thanks,

    Anil N

    Reply
    • Mynda Treacy

      October 3, 2021 at 9:32 am

      Hi Anil,

      Probably, but it’s difficult to help without an example. Please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Mynda

      Reply
  2. Yog Pat

    September 11, 2021 at 7:05 pm

    Hi Friends,

    How to retrieve Top 3 Max value if it’s that value repeating 2 times or more in excel column.
    Function must return max value only.

    Eg.

    3
    8
    3
    5
    4
    7
    7
    5
    7
    150

    Ans.
    Top 1 7
    Top 2 5
    Top 3 2

    Ans.

    Thanks.
    Yog Pat

    Reply
    • Mynda Treacy

      September 11, 2021 at 9:02 pm

      Hi Yog,

      Not sure where you get 2 for the top 3, but if you have Microsoft 365 you can use this formula:

      =LARGE(UNIQUE(FILTER(A2:A11,COUNTIF(A2:A11,A2:A11)>1)),{1;2;3})

      Where your list of numbers is in cells A2:A11.

      Mynda

      Reply
      • Yog Pat

        September 12, 2021 at 12:40 am

        Thanks for reply
        Ans for Top 3 is value “3”

        I don’t have Microsoft 365.
        Any way in older version . Please help.

        Reply
        • Mynda Treacy

          September 12, 2021 at 9:35 am

          Hi Yog,

          You can try doing this in 3 formulas:

          First formula; select cells B2:B11 i.e. the same number of cells that you have values for in column A. Then enter this formula:

          =IF(COUNTIF($A$2:$A$11,$A$2:$A$11)>1,$A$2:$A$11,0) and press CTRL+SHIFT+ENTER to enter it.

          Second formula: select cell C2 and enter this formula:

          =IF(COUNTIF($E$2:E2,E2)<2,E2,0) and press ENTER to enter it then copy down to row 11.

          Third formula: select cell D2 and enter this formula:

          =LARGE($F$2:$F$11,ROW(A1)) and press ENTER to enter it then copy down to row 4.

          If you're still stuck, please post your question on our Excel forum where you can also upload a sample file and we can help you further.please post your question on our Excel forum where you can also upload a sample file and we can help you further.

          Mynda

          Reply
  3. Cyprian Ebong

    July 7, 2021 at 1:51 pm

    Very good lesson

    Reply
    • Mynda Treacy

      July 7, 2021 at 3:51 pm

      Glad it was helpful!

      Reply
  4. GOPAL SHARMA

    August 16, 2020 at 1:28 am

    I HAVE MULTIPLE VALUES IN A CELL
    9, 11, 13, 15, 17 ETC(COMMA MEANS NEXT CELL IN A ROW)
    12, 14, 16, 18, 20
    21, 23, 25, 27, 29
    NOW I NEED TO MATCH ANY VALUE(7,11,14,27) IN A SINGLE ROW
    IF MATCH I NEED THAT CELL VALUE
    “7” DOES NOT EXISTS BUT “11” EXISTS SO I NEED RESULT AS 11 IN 1ST ROW
    14 EXISTS SO I NEED RESULT AS 14 IN NEXT ROW
    27 EXISTS SO I NEED RESULT AS 27 IN NEXT ROW
    WHEN I FINISH FORMULA, CELL MUST SHOW RESULT AS 11 IN 1ST ROW
    OR, ELSE FUNCTION NOT WORKING, I HAVE TRIED SEVERAL TIMES

    Reply
    • Mynda Treacy

      August 16, 2020 at 9:48 am

      Hi Gopal, please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  5. Abbott Katz

    June 11, 2020 at 7:38 pm

    I enjoyed your discussion of INDEX. With respect to the space operator, if you name the ranges via Create from Selection you can write this formula:
    =range1 range2

    and return the value at the intersection

    With Excel 365, you can identify unique pairs of values in non-contiguous fields (say fields 2 and 4) this way:

    =UNIQUE(INDEX(dataset,SEQUENCE(ROWS(dataset),,,),{2,4}))

    Thanks,
    Abbott Katz

    Reply
    • Mynda Treacy

      June 11, 2020 at 7:47 pm

      Yes, naming the ranges will also work with the space operator, as I covered here many years ago now!

      You can also use CHOOSE to trick INDEX into referencing areas on different sheets, but I didn’t want to go down that rabbit hole! Thanks for sharing though 🙂

      Reply
  6. jim

    June 11, 2020 at 7:19 pm

    Hi Mynda,

    How does having to work from home for a change suit you? 😉

    I’m sure I used to be able to con INDEX into using multiple ranges on different sheets by using names ranges but I can’t reproduce it – perhaps a lesson in using the tools given in the way they were intended

    Leaving a parameter blank to select a whole column/row is so useful with dynamic formulae but I’ve found that using the colon operator can make a formula absurdly long and more awkward to parse

    Although this was only an illustration, the example for a space operator uses two range-returning INDEX functions to effectively mimic one straightforward INDEX Function

    another great post; I always look forward to them

    Reply
    • Mynda Treacy

      June 11, 2020 at 7:27 pm

      Cheers, Jim! You can trick INDEX into referencing ranges on different sheets with CHOOSE, but I decided not to go down that rabbit hole 🙂 I agree, I could have thought of a better space operator example!

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.