How VLOOKUP returning more results in a single cell

pmscorca 1,052 Reputation points
2023-04-28T16:02:34.11+00:00

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:
User's image

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
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 122.7K Reputation points
    2023-04-29T05:38:33.4733333+00:00

    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.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Tanay Prasad 2,250 Reputation points
    2023-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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.