3,956 questions
Probably other functions can be used instead of VLOOKUP:
=TEXTJOIN(", ", TRUE, FILTER(B2:B5, A2:A5="John"))
This assumes that the example starts at A1.
How VLOOKUP returning more results in a single cell
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(115.19999999999999, 41%, 21%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EP%3C/text%3E%3C/svg%3E)
pmscorca
1,052
Reputation points
Hi,
I need to use the VLOOKUP function in order to return more results in a single cell by concatenating each result.
F.e. in this scenario:
searching for John in a cell I'd like to have "Pen, Rubber", if possible.
Any suggests to me, please?
Microsoft 365 and Office | Excel | For business | Windows
Accepted answer
-
Viorel 122.7K Reputation points2023-04-29T05:38:33.4733333+00:00
1 additional answer
Sort by: Most helpful
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(12.8, 18%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
Tanay Prasad 2,250 Reputation points2023-05-01T06:38:47.3366667+00:00 Hi,
There is a limit to the number of characters that can be used in a cell, including column headers. If your headers are longer than this limit, they will be truncated when you unpivot your columns.
One solution to prevent the truncation is to shorten the text of your headers before you unpivot. You can do this manually by selecting the cell with the header, clicking on the formula bar, and editing the text to be shorter.
Another option is to use a formula in a separate row to shorten the header text. For example, you could use the LEFT function to extract the first 30 characters of the header, like this:
=LEFT(A1,30)This formula would extract the first 30 characters from the cell in A1. You could then copy this formula across all the columns with long headers, and then use the shortened headers to unpivot your columns.
Best Regards.