Selecting a specific value from a comma-delimited list can be performed with the CHOOSE
function. For example:
=CHOOSE(A1,"A","B","C","D")
where A1 contains the index value, and "A","B","C","D"
is the list.
However, CHOOSE
only works on an actual list embedded in the function, not a reference to a list.
Suppose you have a situation where the list is dynamic, and created and stored in a cell. Say right now, cell B1 contains the comma delimited string "A","B","C","D"
, and it might be a different list at another time.
The requirement is still to select from that list based on the index value in A1, so the equivalent to the pseudo-code:
=CHOOSE(A1,CONTENTS(B1))
Is there a way to accomplish the equivalent of that?
- I'm looking for a generic approach. The list could be anything and any number of terms, which precludes a solution tied to a specific number of items, or items of a particular type or format. It needs to handle the general situation of any list conforming to the CSV standards. However, the list will not be so large as to exceed any Excel limitations.
- Note that this is different from the action of the INDIRECT function, which won't work for this purpose.
- The solution needs to behave like a function (automatically stay current with changing content). That precludes a solution requiring manual intervention. An automatically triggered VBA solution would not be precluded if that is the only possible solution, but it is undesirable because VBA would not always be available, and it would limit the ability to transfer the solution to other spreadsheet applications.
- Use of a helper column is not precluded, but the variable nature of the data would make it impractical for a solution that involves parsing the list into separate cells.
mid
them.SPLIT
function (exists in VBA but is not accessible from the worksheet), but use it withINDEX
rather thanCHOOSE
. For example,=INDEX(SPLIT(B1,","),1)
.