SOLVED

New Contributor

# How to eference a particular cell in anamed range?

Win  10 Home 21H1

Microsoft 365

Given the named range Dta_Vals [Dta Vals, 11,13. 17], how do I reference the 13?

=Dta_Vals(2) results in a "REF!" error.

7 Replies

# Re: How to eference a particular cell in anamed range?

Does INDEX(Dta_Vals,3) work?

# Re: How to eference a particular cell in anamed range?

No cigar, but since INDEX(Dta_Vals,2) works, I would award at least several cigarets. I was hoping for something a bit more simple, but I recon I'm being too choosey..
best response confirmed by ibud00 (New Contributor)
Solution

# Re: How to eference a particular cell in anamed range?

As @mtarler points out, the spreadsheet way of referencing elements of an array involves the use of the powerful, but somewhat cumbersome, INDEX function

``= INDEX(Dta_Vals,2)``

A 2D array would require both row and column indices.  If you are using Excel 365 it is possible to tidy the syntax up by defining a Lambda function

``````Dta_Valsλ
= LAMBDA(k, INDEX(Dta_Vals,k))``````

(where the Greek λ is is something I use to distinguish the function name from the range name).  This allows the worksheet formula to be written in a mathematically more standard form

``= Dta_Valsλ(2)``

Whether is is worth the effort is another matter, especially as users are unlikely to understand what is going on.

# Re: How to eference a particular cell in anamed range?

This is the solution for which I was hoping. Now, to develop my LAMBDA skills. How do I mark this  as the Best Response?"

# Re: How to eference a particular cell in anamed range?

From 3-dots menu you may unmark previous Best Response (using Not a best response) and after that to mark another post.

# Re: How to eference a particular cell in anamed range?

Are you making progress?  First and foremost, you need to be using Excel 365 as opposed to any legacy versions.  I have attached a file that contains the Lambda function to read the particular array by index.  The function returns a cell reference so the second function returns and index from the reference by comparing column numbers.

# Re: How to eference a particular cell in anamed range?

Thanks! I was able to change Peter Bartholomew's response to the best one.