• 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

Excel Functions that Return References

You are here: Home / Excel Formulas / Excel Functions that Return References
Functions that Return References
June 25, 2020 by Mynda Treacy

Using a formula to return a reference to a range of cells allows us to generate a reference on the fly based on the shape of the data or criteria we specify. As our data grows these formula generated references can automatically update to include new data. This has huge efficiency advantages over hard coding the reference because we no longer need to edit formulas to update references when they change.

Many of us know the OFFSET function returns a reference to a range of cells, but there are actually 8 Excel functions that return a reference to a range:

  1. OFFSET
  2. INDEX
  3. XLOOKUP (Excel for Microsoft 365 only)
  4. CHOOSE
  5. SWITCH (new in Excel 2019)
  6. IF
  7. IFS (new in Excel 2019)
  8. INDIRECT

Each function has their pros and cons. Let’s take a look at them in turn.

Note: I’m not going to cover each function in detail. If you’re not familiar with any of the functions, there are links below to detailed tutorials.

Watch the Video

Subscribe YouTube

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

Examples of Excel Functions that Return References

1. OFFSET Function

Step by step tutorial on the OFFSET function.

Syntax reminder:

=OFFSET(reference, [rows], [cols], [height], [width])

In the image below, cell B14 contains an OFFSET formula that returns the range E4:E7. The results spill to the cells below because I have Excel for Microsoft 365 and the new Dynamic Arrays, but essentially this formula returns a reference:

Excel Functions that Return References - OFFSET 1

Earlier versions of Excel will return an error because Excel doesn’t know what to do with the reference returned. However, if we wrap the OFFSET formula in the SUM function it adds the values in the range E4:E7. And if you use the evaluate formula tool you can see the reference being returned, as shown below:

OFFSET 2

The above examples returned a single column however, you can also return rows, single cells or even a whole table (see example below). References to whole tables is useful for dynamic named ranges which can be referenced in formulas or as the source for PivotTables etc.

OFFSET 3

Notice the formula uses COUNTA to determine the height and the width of the table. The range in the COUNTA formulas reference an extra row and column to allow for growth in the table. In practice you would reference many more rows and/or columns in line with how much you expect the table to grow by.

OFFSET Pros

The biggest benefit to OFFSET is that it only takes one formula to return a reference to a single cell, column, row, or table.

We can easily determine a dynamic starting cell reference using the rows and cols arguments to find the starting cell for the range. e.g.

=OFFSET(A3,1,MATCH(B13,B3:E3,0),COUNTA(B4:B8),1)

Tells Excel to move one row down from A3 and use MATCH to find how many columns to move across to find the starting cell reference, which is E4. COUNTA then finds the end cell reference by counting how many cells high the range should be, which returns E7.

OFFSET Cons

It’s a volatile function, which means it recalculates every time a cell is edited and even in some other circumstances which can result in slow workbooks.

2. INDEX Function

Step by step tutorial on the INDEX function.

Syntax reminder:

=INDEX(array, row_num, [column_num])

Like OFFSET, the INDEX function can also return a reference to a single column or row, or a whole table. The example below returns the reference to the Sales values in column E and you’ll see the approach is slightly different in that the starting cell, E4, is hard keyed in the formula before the colon operator and we only use INDEX to return the reference to the last cell in the range, E7:

Excel Functions that Return References - INDEX function example 1

Note: We can use INDEX on both sides of the colon operator to return a range that has a dynamic starting and ending cell, as you can see below:

=INDEX(E4:E7,MATCH("Bikes",C4:C7,0)) : INDEX(E4:E7,MATCH("Clothing",C4:C7,0))

We can see in the image below that this formula returns the sales for Bikes through to Clothing:

Excel Functions that Return References - INDEX function example 2

Again, we can wrap the INDEX formula in SUM to add up the values in the range returned by INDEX:

INDEX function example 3

We can also return a whole table including the headers:

INDEX function example 4

INDEX Pros

INDEX is non-volatile and far more efficient than OFFSET and INDIRECT, and it can return a reference to a single cell, column, row, or table.

INDEX Cons

If your reference also requires a dynamic starting cell reference, then you need to use INDEX on both sides of the colon

Note: When using INDEX on both sides of the colon operator it is always recalculated upon opening the workbook, which means it is momentarily volatile, but not once the initial recalculation has run. It’s still more efficient than OFFSET and INDIRECT

3. XLOOKUP Function (Excel for Microsoft 365 only)

Step by step tutorial on the XLOOKUP function.

Syntax reminder:

=XLOOKUP(lookup_value, lookup_array, return_array, [If_not_found], [match_mode], [search_mode])

Unlike OFFSET and INDEX, XLOOKUP can only return a reference that is one column wide for vertical references, or one row high for horizontal references. In other words, XLOOKUP can’t return the reference to a whole table.

Excel Functions that Return References - XLOOKUP function example 1

And of course, we can nest XLOOKUP inside of other functions that take references, like SUM:

XLOOKUP function example 2

XLOOKUP Pros

XLOOKUP is a much easier function to learn and use, plus it’s super efficient.

XLOOKUP Cons

XLOOKUP can only return a reference to a single cell, row or column, not a whole table consisting of multiple rows and columns. It’s also only available to Excel for Microsoft 365 users.

4. CHOOSE Function

Step by step tutorial on the CHOOSE function.

Syntax reminder:

=CHOOSE(index_num, value1, [value2],...)

With CHOOSE we can list several different references to ranges and with the first argument specify which reference to return. In the example below I’ve used the MATCH function to find the reference number for Sales:

CHOOSE function example 1

Cell B13 (above) contains a data validation list that allows me to choose different references to return, as you can see below:

CHOOSE

With CHOOSE we can also trick VLOOKUP into looking left by specifying which is column 1 and which is column 2. In the image below you can see that column D is listed first and column C is listed second. The array of {1,2} simply returns both references to VLOOKUP:

CHOOSE function example 2

CHOOSE Pros

CHOOSE gives us the ability to rearrange the order of columns or rows and reference non-contiguous ranges, including references on other sheets.

CHOOSE Cons

CHOOSE on its own can’t return a dynamic range. You would need to also use another function, like OFFSET or INDEX in CHOOSE’s value arguments.

5. SWITCH Function (New in Excel 2019)

Step by step tutorial on the SWITCH function.

The SWITCH function is similar to CHOOSE, except is has a slightly different structure to its syntax.

Syntax reminder:

=SWITCH(expression, value1, result1, [default_or_value2], [result2],...)

Excel Functions that Return References - SWITCH function example 1

Again, you can link the expression argument to a cell containing a data validation list which allows the user to choose a different range to return.

SWITCH

And you can wrap SWITCH in other functions that take references, like SUM:

SWITCH function example 2

SWITCH Pros

Those familiar with SWITCH from other programming languages will find this an easy function to use.

SWITCH Cons

Like CHOOSE, SWITCH on its own cannot return a dynamic range and it can’t return a reference to non-contiguous ranges.

6. IF Function

Step by step tutorial on the IF function.

Syntax reminder:

=IF(logical_test, value_if_true, [value_if_false])

We can replicate CHOOSE and SWITCH with nested IF functions:

Excel Functions that Return References - IF function example 1

Again, users of Excel 2019 and earlier would need to either wrap the formula in another formula that uses the reference returned by IF, like SUM (shown below), or define a name.

IF function example 2

IF Pros

IF has been around forever and many Excel users are already comfortable using it, which will make it easy to adopt for the use of returning references.

IF Cons

The IF function on its own cannot return a dynamic range. It’s not an efficient formula as each logical test will be calculated until a match is found, at which point it stops calculating. That said, if it’s not used in bulk then the calculation burden will be insignificant.

7. IFS Function (new Excel 2019)

Step by step tutorial on the IFS function.

If you have Excel 2019 or Excel for Microsoft 365, you can use IFS instead of having to nest IF formulas.

Syntax reminder:

=IFS(logical_test1, value_if_true1, [logical_test2], [value_if_true2]...)

Excel Functions that Return References - IFS function example 1

Like IF, IFS can be used in any formula that takes a reference:

IFS function example 2

IFS Pros

IFS is easier to write than a nested IF.

IFS Cons

Like IF, IFS cannot return a dynamic range on its own and will evaluate every logical test until one returns TRUE. As it’s only available in Excel 2019 onward, not many people will have access to it yet.

8. INDIRECT Function

Step by step tutorial on the INDIRECT function.

Syntax reminder:

=INDIRECT(ref_text, [a1])

The INDIRECT function returns a reference specified by a text string as shown in the example below:

Excel Functions that Return References - INDIRECT function

The cell reference text string can also be obtained by:

  • referencing another cell, or
  • generate it by nesting other functions

You can also use the ampersand (&) symbol to concatenate text and build a text string that way.

INDIRECT Pros

Probably the biggest positive for INDIRECT is that references hard keyed in the formula don’t update when rows/columns are inserted in the worksheet. E.g. =INDIRECT("E4:E7") will not change if a row is inserted above row 4 or to the left of column E.

Also, the reference can be built using text strings e.g. =INDIRECT("B27:B"&ROW(B35)) will return the reference B27:B35

INDIRECT Cons

INDIRECT is a volatile function and inefficient.

What about the FILTER Function?

You may be wondering about the FILTER function. After all, it spills the results like the examples above and can be nested inside functions like SUM and AVERAGE. However technically FILTER is not returning a reference.

This is best illustrated if you use the Evaluate Formula tool on any of the SUM examples above. You’ll see these nested formulas return cell references, but if you do this with FILTER it returns an array of values. And while this is fine for SUM, keep in mind that it won’t be any use if you wanted to use FILTER as a source for a PivotTable, as this requires a reference.

The Best Excel Function that Returns a Reference

With 8 different functions that can return references, you’re probably wondering which one is best.

There isn’t one that’s best, but if you want to return a reference to a table, I’d use INDEX because it’s not volatile like OFFSET.

If you need to return a reference to non-contiguous cells or you want to rearrange the order of columns or rows, then use CHOOSE.

And if you only need a single column or row of cells, then XLOOKUP is super easy to use and very efficient for Excel to calculate.

Functions that Return References

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:Excel STOCKHISTORY FunctionExcel STOCKHISTORY Function
Next Post:Animating Excel Chartsanimating excel charts

Reader Interactions

Comments

  1. ControlsGirl

    May 14, 2022 at 4:56 am

    You are phenomenal! These videos demonstrate how these functions can be utilized as well as how to use them.

    Reply
    • Mynda Treacy

      May 14, 2022 at 8:52 am

      Glad it was helpful

      Reply
  2. AccessMaster

    November 14, 2021 at 12:40 am

    What do you mean by volatile and non-volatile of a function?

    Reply
    • Mynda Treacy

      November 14, 2021 at 12:36 pm

      A volatile function is one which recalculates every time Excel recalculates, irrespective of whether the precedent data/formulas that the formula depends on have changed. This can put a huge strain on Excel’s performance and therefore, it is recommended that they are avoided where possible.

      Reply
  3. Alan Elston

    February 20, 2021 at 3:18 am

    Hello Mynda,
    I am not very clued up on Excel spreadsheet functions, and so I needed a quick Google search on Excel spreadsheet function that return range object references.- It brought me here, I got the info I needed, so thanks.

    _..I hit onto something a bit strange in some VBA work: I noticed that the single argument VBA Range(” “) thing appeared to do something similar to the VBA Evaluate(” “) thing, provided that what I put inside the ” ” is an Excel spreadsheet function that returns a range object.
    ( I have had the feeling for some time that something like =A1 written in a cell is actually returning a range object, even if inside Excel that may not be immediately obvious. What I mean by that is that, for example, this works

    Dim Rng As Range
    Set Rng = Evaluate(“=A1”)

    )
    That is not new to me, but the 5 code lines below are new to me, and would be to quite a few VBA uses also, I expect..

    So I guess to answer your question in the video ..”if I have any clever ways to use the functions ” – well, I am not sure yet if this is clever, but it is something new, at least to me..
    These all work in VBA:

    Range(“=INDIRECT(“”A1″”)”).Value = “From Indirect”
    Range(“=INDEX(A1:B2,1,1)”).Value = “From Index”
    Range(“=CHOOSE(1,A1,B1)”).Value = “From Choose”
    Range(“=IF(1=1,A1)”).Value = “From If”
    Range(“=OFFSET(A1,0,0,1,1)”).Value = “From Offset”

    I am not sure of possible uses of this yet.. But until I read this Blog of yours my thinkings were restricted to this sort of thing, as many people’s thinking is
    Range(“=A1”).Value = “Something”
    ( Or as more typically seen, this sort of thing
    Range(“A1”).Value = “Something”
    )
    Just to clarify again what i am saying.. The new thing, to me, is that an Excel spreadsheet formula appears to work in the single argument VBA Range(” “) thing , provided that
    _the formula returns a range object,
    or put another way, provided that
    _ the formula returns a reference to a range

    Alan

    Reply
    • Mynda Treacy

      February 20, 2021 at 9:35 am

      Thanks for sharing, Alan!

      Reply
  4. Matthias

    July 2, 2020 at 6:33 am

    Hi Mynda,

    Thanks for this good and helpful overview!

    I used it today to get a reference to the last cell of a column with Index and CountA.

    Thanks again,
    Matthias

    Reply
    • Mynda Treacy

      July 2, 2020 at 9:19 am

      Great to hear, Matthias!

      Reply
  5. Excel Gazi Deniz Hodja

    June 27, 2020 at 1:13 am

    Dear Mynda,

    Regarding the slightly more effective use of INDIRECT() to return a reference (a range of cells), one can take the following steps:

    (0) Select the range B7:E7, i.e. the range of data cells inclusive of the column headers.

    (1) Using the “Create Names From” Excel shortcut CTRL+SHIFT+F3, define AUTOMATICALLY CREATED names for each individual vertical range of cells in columns B through E. This will result in 4 automatically created names “Year”, “Category”, “Product” and “Sales”.

    (2) Write in cell B13 the name of the cells (i.e. the name of the range) which you want to display in B14:B17. For example: Sales.

    (3a) If you’re using Excel 365, write in cell *B14* the following formula:
    =INDEX(B13) and let Excel 365 spill the resulting array onto cells B14 through B17.

    (3b) If you’re using Excel 201x, select the cells B14:B17, press the button F2 and write the same formula; however, this time enter it with CTRL+SHIFT+ENTER as an array formula.
    Voila!

    In case some column headers are made up of multiple words separated by blank spaces, then the formula needs to be changed to:
    =INDIRECT(SUBSTITUTE(B13,” “,”_”))
    since Excel replaces blank spaces with the underline character when creating names from column and/or row headers of a data table.

    I shall enter this as a comment below your blog post too. I have been following you together with my students since Spring 2016. And thanks to my students of the Spring semester in 2017, I also got the same “I simply EXCEL” t-shirt as yours. (:D

    Reply
    • Mynda Treacy

      June 27, 2020 at 8:56 am

      Thanks for sharing! To summarise your point, INDIRECT can evaluate a defined name. Note: In point 3a I think you mean =INDIRECT(B13) not =INDEX(B13)

      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.