If I want to refer to the text in Sheet 1 that has an enumeration number in Sheet 2 as an array in Sheet 3, how do I do that?

RynerZ 21 Reputation points
2020-12-17T10:29:50.39+00:00

48937-image.png

49124-image.png

49172-image.png

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,362 questions
Accepted answer
  1. Lz._ 8,806 Reputation points
    2020-12-17T12:47:01.32+00:00

    Hi @RynerZ

    Assuming Excel 365 with dynamic arrays + FILTER and LET functions:

    Demo

    in Sheet3!B1:

    =LET(
    SplitString,FILTERXML("<r><n>" & SUBSTITUTE(Sheet1!A1,",";"</n><n>") & "</n></r>"; "//n"),
    Lookup,VLOOKUP(SplitString,Sheet2!A1:B100,2,FALSE),
    FilterOutNA,FILTER(Lookup,ISNUMBER(Lookup)),
    "[" & TEXTJOIN(",",, IFERROR(FilterOutNA,"No match")) & "]"
    )

    Corresponding workbook avail. here

    Feel free to upvote Add a Split dynamic array function on Excel UserVoice

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Emi Zhang-MSFT 11,566 Reputation points Microsoft Vendor
    2020-12-18T09:00:24.26+00:00

    Hi @RynerZ ,
    Try to add a new Sheet and transform Sheet 1 to the new Sheet- Split it- and transform the data:
    49338-129.gif

    Use VLOOKUP function to get the value from Sheet 2:

    49425-130.gif

    Go to Sheet 3 and enter the formula:

    ="["&TEXTJOIN(",",TRUE,Sheet4!B1:B3)&"]"

    49280-131.gif

    If the response is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.