Forum Discussion
Reference Question
I am trying to figure out how to use the Lookup formulas in Excel to get what I need. I have a 2x100 table of values. The left column, "A", has a series of unique numbers. The right column. "B"...
DonMirabella If you are on Excel 2019 you can use TEXTJOIN in a three step solution as shown in the attached workbook. First create a pivot table, then TEXTJOIN, and then text-to-columns. Not very elegant, but fairly easy and workable if this is something you do once or only every now and then.
Otherwise, use PowerQuery.
Query the table in the two columns, add an index column, pivot the index with column A as the value (without aggregation). Then merge all the columns except the first one, using a space as delimiter. Trim the lot and split the column by space delimiter to get the table you desire. Load back to Excel.