• 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
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

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
Mynda Treacy

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Excel Formulas Posts

Summarize Months to Quarters

Excel Formulas to Summarise Monthly Data into Quarters

3 ways (good, better, best) to summarize monthly data into quarters using formulas. Lots of examples and sample file to download.
Excel BYROW and BYCOL Functions

Excel BYCOL and BYROW Functions

Excel BYCOL and BYROW functions fundamentally change the way we write formulas that calculate across columns and down rows.
python in excel natively

How to Use Python in Excel Natively

How to use Python in Excel natively using libraries like Pandas, NumPy, Matplotlib, Seaborn and more for analysis and spectacular charts!
excel dynamic named ranges

Excel Dynamic Named Ranges

Excel Dynamic Named Ranges update automatically to include new data in the ranges referenced in your formulas and PivotTables etc.
functions for financial modelling

Excel Functions for Financial Modeling

Top 23 must know Excel functions for Financial Modeling. Includes example Excel file and step by step instructions.
excel formula by example

Excel Formula by Example

Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.
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 Labs (Formerly, Advanced Formula Environment)

Excel Labs is a long awaited, new improved way to write, name and store Excel formulas, including LAMBDAS with the help of AI.
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.


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...

Popular 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

239 Excel Keyboard Shortcuts

Download Free PDF

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 Office Scripts
  • 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

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

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

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.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x