Formula for multiline cell input to output multiline cell

Question

Rough Outline:
I am looking for a formula to match up values from a list that can contain single line cells and multiline cells to a table with single values = single values. (Not sure if this properly explains my need)

Requirements:
Ideally this will work on Excel Online, without VBA support as I believe UDFs are disabled.

Breakdown:
I have a list of parts. Each part is assigned a code number in a separate table (on separate sheet) I have a formula to check if the part exists in this table and output the code number, but this only works for single line entries. Due to how we are keeping record, some entries will have multiple parts, and thus, multiple lines in a cell.

Example:
I would like my main sheet to have parts recorded, and in the next cell a formula to output their code numbers.
So, assuming Part A = Code 1, Part B = Code 2, Part C = Code 3 (As defined in another table)

A1 = Part A          | B1 = Code 1  

A2 = Part A          | B2 = Code 1
     Part C          |      Code 3  

A3 = Part B          | B3 = Code 2

and so forth. Cells with multiple outputs (i.e. B2) will be in the same cell as a multiline.

Excel Multiline Expectaions Example

Editted the example for better clarity.

Scott Craner Scott Craner · Accepted Answer · 2021-07-14T14:03:27

Use FILTERXML to split and return the array to XLOOKUP that returns the return array to TEXTJOIN:

=TEXTJOIN(CHAR(10),TRUE,XLOOKUP(FILTERXML("<t><s>"&SUBSTITUTE(A2,CHAR(10),"</s><s>")&"</s></t>","//s"),E:E,F:F,"",0))

Change both the CHAR(10) to what ever the delimiter is between the inputs. You screen shot shows the return, but your verbiage shows & Change to what you need/want.

Make sure your wrap text is set to true on the output cells.

Formula for multiline cell input to output multiline cell

1 Answers