Forum Discussion

Copper Contributor

Jul 15, 2021

Solved

Range name in formula

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

Formulas and Functions

HansVogelaar
Jul 15, 2021
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.

MVP

Jul 15, 2021

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.

PeterBartholomew1
Silver Contributor
Jul 15, 2021
HansVogelaar
There are situations in which multiple ranges can be 'lumped' together.
- HansVogelaar
  MVP
  Jul 15, 2021
  PeterBartholomew1
  
  Hi Peter,
  
  I feel dense. I don't get your point.
  - PeterBartholomew1
    Silver Contributor
    Jul 15, 2021
    HansVogelaar
    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 SergeiBaklan 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!
David2190
Copper Contributor
Jul 15, 2021
INDIRECT!
Of course you can.
Thanks Hans.

Resources