54

For example, I have a named range A10—A20 as Age; how do I get Age[5] which is same as A14.

I can write "=A14" but I did like to write "=Age$5" or something similar.

M--
  • 25,431
  • 8
  • 61
  • 93
Champ
  • 1,291
  • 4
  • 16
  • 32

6 Answers6

73

You can use Excel's Index function:

=INDEX(Age, 5)
Alex P
  • 12,249
  • 5
  • 51
  • 70
  • 9
    Do you know if there's a way to make this work with relative selections, so that the formula can be "dragged down"/applied across several cells in the same column? – leifericf Jul 30 '14 at 07:51
  • How could one use index to access a subset of age, e.g. Age 5-7? – DavveK Sep 24 '18 at 10:22
  • 2
    I figured out the answer: INDEX(NamedRange,5):INDEX(NamedRange,7)) – DavveK Sep 24 '18 at 10:30
16

"Do you know if there's a way to make this work with relative selections, so that the formula can be "dragged down"/applied across several cells in the same column?"

To make such selection relative simply use ROW formula for a row number in INDEX formula and COLUMN formula for column number in INDEX formula. To make this clearer here is the example:

=INDEX(named_range,ROW(A1),COLUMN(A1))

Assuming the named range starts at A1 this formula simply indexes that range by row and column number of referenced cell and since that reference is relative it changes when you drag the the cell down or to the side, which makes it possible to create whole array of cells easily.

Reg Edit
  • 6,719
  • 1
  • 35
  • 46
Dawid Stróżak
  • 191
  • 1
  • 6
  • 6
    Doesn't this ruin the whole concept of named ranges, if I need to know the cell that the named range starts at? – Dana Nov 30 '15 at 19:09
  • It depends on what you want to do, most of the times named range will start at the top. I have not done anything in Excel for a while but I remember that when I was creating spreadsheet model in the past I was able to create dynamic named range that had various positions depending on filter so I guess instead of hard coded A1 for ROW or COLUMN you would use something dynamic to return the reference. I am cannot test this right now as I am on Linux. – Dawid Stróżak Dec 01 '15 at 13:54
  • 1
    @Dana, I don't think that's the case; `row(A1)` and `column(A1)` just produce the coordinates `(1,1)`, which you use to index the first cell in your named range. It would work the same if `named range` did not start at A1. – AnjoMan Feb 19 '16 at 19:05
  • @AnjoMan, You're right, I tested it! This is great way to enable one to index the reference relative to the named range. Dawid Stróżak, most of the time when I use named Ranges I put them anywhere I want access to a value or range of values I don't want to hunt for all the time. I especially do this when writing in VBA. Most of my named ranges are anywhere in a sheet, never even close to the top, let alone mostly at the top. – Cyberchipz Apr 13 '16 at 16:51
  • @Cyberchipz - rather than making a drastic edit to this answer, it would be better for you to write your own answer, "Expanding on Dawid's answer..." – Mogsdad Apr 13 '16 at 18:40
  • @Mogsdad Thanks for the advice; but, I'm ok with this if you are. His and the other answers are correct insofar as they go. I liked the first answer best because it was simple, and I as an experienced user of Excel immediately could expand upon it. In truth, it could have been a totally different question, however it is related as an expansion to the first question, that I think that those looking for more information could benefit from it. A true answer would have both parts, I think. ;-) I think from both I could make one true answer. lol – Cyberchipz Apr 13 '16 at 18:47
3

There are a couple different ways I would do this:

1) Mimic Excel Tables Using with a Named Range

In your example, you named the range A10:A20 "Age". Depending on how you wanted to reference a cell in that range you could either (as @Alex P wrote) use =INDEX(Age, 5) or if you want to reference a cell in range "Age" that is on the same row as your formula, just use:

=INDEX(Age, ROW()-ROW(Age)+1)

This mimics the relative reference features built into Excel tables but is an alternative if you don't want to use a table.

If the named range is an entire column, the formula simplifies as:

=INDEX(Age, ROW())

2) Use an Excel Table

Alternatively if you set this up as an Excel table and type "Age" as the header title of the Age column, then your formula in columns to the right of the Age column can use a formula like this:

=[@[Age]]
ChrisB
  • 3,024
  • 5
  • 35
  • 61
  • "..then your formula in columns to the right of the Age column..." unless I'm misunderstanding your answer I think question was asking if there was a way to reference a cell in a named column – Gabriel Fair Sep 01 '17 at 20:52
2

I've been willing to use something like this in a sheet where all lines are identical and usually refer to other cells in the same line - but as the formulas get complex, the references to other columns get hard to read. I tried the trick given in other answers, with for example column A named as "Sales" I can refers to it as INDEX(Sales;row()) but I found it a bit too long for my tastes.

However, in this particular case, I found that using Sales alone works just as well - Excel (2010 here) just gets the corresponding row automatically.

It appears to work with other ranges too; for example let's say I have values in A2:A11 which I name Sales, I can just use =Sales*0.21 in B2:11 and it will use the same row value, giving out ten different results.

I also found a nice trick on this page: named ranges can also be relative. Going back to your original question, if your value "Age" is in column A and assuming you're using that value in formulas in the same line, you can define Age as being $A2 instead of $A$2, so that when used in B5 or C5 for example, it will actually refer to $A5. (The Name Manager always show the reference relative to the cell currently selected)

erlative named range

M--
  • 25,431
  • 8
  • 61
  • 93
Joubarc
  • 1,206
  • 10
  • 17
1

Add a column to the left so that B10 to B20 is your named range Age.

Set A10 to A20 so that A10 = 1, A11= 2,... A20 = 11 and give the range A10 to A20 a name e.g. AgeIndex.

The 5th element can be then found by using an array formula:

=sum( Age * (1 * (AgeIndex = 5) )

As it's an array formula you'll need to press Ctrl + Shift + Return to make it work and not just return. Doing that, the formula will be turned into an array formula:

{=sum( Age * (1 * (AgeIndex = 5) )}
M--
  • 25,431
  • 8
  • 61
  • 93
Drew
  • 11
  • 1
0

To read a particular date from range EJ_PAYDATES_2021 (index is next to the last "1")

=INDEX(PayDates.xlsx!EJ_PAYDATES_2021,1,1)  // Jan
=INDEX(PayDates.xlsx!EJ_PAYDATES_2021,2,1)  // Feb
=INDEX(PayDates.xlsx!EJ_PAYDATES_2021,3,1)  // Mar

This allows reading a particular element of a range [0] etc from another spreadsheet file. Target file need not be open. Range in the above example is named EJ_PAYDATES_2021, with one element for each month contained within that range.

Took me a while to parse this out, but it works, and is the answer to the question asked above.

Steve Walton
  • 1