Range name in formula

New Contributor


I want to use a named range in an index/match formula, but I want the range used to be determined by text in another cell. When the text in this other cell changes to another named range, the formula uses the new named range.


How would I go about doing that? The formula works splendidly with the range named specifically, but I need it to be dynamic.



8 Replies
best response confirmed by David2190 (New Contributor)

@David2190 Let's say you enter the name in cell A1.

You can then use INDIRECT(A1) in your formulas to refer to the range whose name is in A1.



Of course you can.
Thanks Hans.

@Hans Vogelaar 

There are situations in which multiple ranges can be 'lumped' together.



@Peter Bartholomew 

Hi Peter,


I feel dense. I don't get your point.

@Hans Vogelaar 

If to translates, names are

A1 - Name

C1:D1 - Headings

C2:D6 - Data

@Hans Vogelaar 

INDIRECT is the answer to the OP, but it has never been my favourite function because of its volatility.  I set out to show an alternative that works when the ranges are collected together as a table.  I could also have suggested 

= LET(
   option, XMATCH(Name, Heading),
   SUM(CHOOSE(option, MyRange, YourRange)) )

As @Sergei Baklan points out, besides being aggressively 365, my solutions never use any direct referencing.  That just stems from a remark I made that the A1 notation is an abomination that should never have been permitted notation anywhere within an electronic spreadsheet; I feel obliged to stick with my own assertion, even if it obscures my formulas for others.


I apologise for causing confusion; it is me not you!

@Peter Bartholomew 

On the other hand that narrows the solution. Let assume named ranges with data are kept separately, e.g. in different worksheets.

@Sergei Baklan 

I confess I have very little experience of links between workbooks or between a workbook and a Word document.  Even on those occasions, the focus tended to be on introducing VBA or PowerQuery to eliminate any direct links.   Very rarely, I have used defined Names from the data source (rather as one would do from VBA) but I suspect that requires the workbook to be open, so I would still be back to relying on VBA to ensure the workbook is open (or even exists).