Apr 20 2022 06:47 AM
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.
Apr 20 2022 07:45 AM
Apr 20 2022 08:18 AM
Apr 20 2022 08:44 AM - edited Apr 20 2022 08:45 AM
SolutionAs @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.
Apr 21 2022 03:29 AM - edited Apr 21 2022 03:35 AM
This is the solution for which I was hoping. Now, to develop my LAMBDA skills. How do I mark this as the Best Response?"
Apr 21 2022 06:52 AM
From 3-dots menu you may unmark previous Best Response (using Not a best response) and after that to mark another post.
Apr 21 2022 10:21 AM
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.
Apr 21 2022 12:46 PM - edited Apr 21 2022 12:47 PM
Thanks! I was able to change Peter Bartholomew's response to the best one.
Apr 20 2022 08:44 AM - edited Apr 20 2022 08:45 AM
SolutionAs @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.