SOLVED

How to eference a particular cell in anamed range?

Copper 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 (Copper Contributor)
Solution

@ibud00 

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.

@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?"

@ibud00 

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

@ibud00 

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.

1 best response

Accepted Solutions
best response confirmed by ibud00 (Copper Contributor)
Solution

@ibud00 

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.

View solution in original post