How to eference a particular cell in anamed range?

New Contributor

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
Does INDEX(Dta_Vals,3) work?
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)


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


= 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.

@Peter Bartholomew 

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


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


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.

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