Naming a range and referring to it in Excel?

Is it possible to put a named range in a formula like this?

=ISNUMBER(MATCH(D42,someworksheet!A:A,0))

Specifically where it says A:A ?

You can use the INDIRECT() function to do this.

It's very straightforward: You simply build a string that matches the literal string of the desired address/reference. This building of a string is done exactly as any other string building you do which means all the ways to gather the parts are also available to you.

So the concept would be to place the INDIRECT() function immediately after the "D42," in the MATCH() function.

When using Named Ranges inside INDIRECT() it is very important to consider what is returned as that determines how to refer to the Named Range in the function. BROADLY, if the Named Range "Refers to" literal text (like your A:A COULD be), then you enter the Named Range as is, no doublequotes around it:

=INDIRECT(SomeName)

In this case, SomeName "Refers to" ="A:A".

If you use the doublequotes everyone tells you Named Ranges must have inside INDIRECT() when the Named Range "Refers to" literal text, you will get a #REF! error. No bueno.

If a Named Range refers to a viable address in viable address form, then you MUST use the doublequotes inside the INDIRECT() function. For example, if SomeOtherName "Refers to" someworksheet!A:A (no quoting in the "Refers to" value — it is a viable address, one you could copy and paste directly into a formula and it would work exactly as you found it), then you would use it as:

=INDIRECT("SomeOtherName")

So, you can use either way to define your Named Range. However, if the Named Range "Refers to" a viable address, Excel WILL add to it the sheet name from the sheet you have active when you create and define the Named Range. All well and good if you want the Named Range to include THAT sheet in what it gives to INDIRECT() or if you don't but are happy to go to the trouble of using string techniques to remove that sheet name from it before making INDIRECT() evaluate it.

If you use a literal string ("A:A" — note the doublequoting makes it a literal string, not a viable address), then you'd use the following formula: (using the names above)

=ISNUMBER(MATCH(D42,  INDIRECT("someworksheet!"&SomeName),  0)))

INDIRECT() will join the doublequoted sheet name and "!" with the literal string from SomeName ("A:A") to get "someworksheet!A:A". What's inside the doublequotes would be a valid address/reference, so INDIRECT()` will return your desired result.

If you are OK with the Named Range returning the sheet name along with the "A:A", then you would use the following:

=ISNUMBER(MATCH(D42,  INDIRECT("SomeOtherName"),  0)))

Very important the doublequotes around the Named Range. In this case, it will get the viable address with no string building past the one step and return the values it gives just like the other example.

If you want exactly what you mentioned in the question, use the first one.

There are many reasons to prefer one approach over the other. Your exact use/needs will determine which you would take. Neither has any theoretical preference.

In general, any time you need to build an address and use it as a real address, not a string, INDIRECT() is your friend. There are steps between literally specifying an address in a formula and stepping away like you do with INDIRECT(). For instance, OFFSET() can build an address, but does so directly from parts that can be specified in a variety of ways, but none of them can be text like "someothersheet" — so sort of in between directly writing an address and completely creating it out of text.

It has a great many uses. The above barely touches on them all. It's so useful that you ought to look it up on help sites and come to understand it.

In the past for almost anything, and currently for spreadsheets in which it might be used, say, a few hundred thousand times or in which the rest of the calculation burden is similarly huge, it did have a drawback. It is a volatile function and any cell with it in it is recalculated every time Excel calculates. Not just when Excel thinks it needs to be because something affecting it changed. With today's computers and memory levels, this is of no concern whatever anymore unless, as mentioned, one has an otherwise massive calculation load, or would use it enough to be a massive load itself. And massive means, say, 100,000 or more rows and some reasonable number of columns. Something to consider if your spreadsheet calculates slowly. Also, modern Excel has the LET() function and if your use calculated the same address more than once in any given cell, LET() will allow you to cut the number of times it is calculated to only once, not several times. So it is becoming more useful every day!