vlookup to obtain multiple items from the same data set with concatenation

Question

i have a tabe data set id and description, its a meal plan, i am trying to use vlookup to select 1 item from the data set then concatenate the results of another vlookup on the same table as an example i am trying to choose id 1 and then add meal id 9 to the results output, ( 1,9) am i trying the eimpossible

Answer 1

Hi @Tim Cox,

If you are running Excel 365, then try the formula below. I have applied it to cell E2 and I have used comma as separator between sentences.

=BYROW(D2:D9, LAMBDA(a, ARRAYTOTEXT(IFNA(XLOOKUP(--REGEXEXTRACT(a, "\d", 1), A2:A10, B2:B10), ""))))

If you want you can use this formula instead of previous one:

=BYROW(D2:D9, LAMBDA(a, TEXTJOIN(CHAR(10),, IFNA(XLOOKUP(--REGEXEXTRACT(a, "\d", 1), A2:A10, B2:B10), ""))))

In this case select range E2:E9 go to Format Cells > Alignment > Wrap text > OK.

Hope this helps.

IlirU

Answer 2

VLOOKUP can only return one value at a time, but multiple VLOOKUP results can be concatenated in a single formula.

Assume the table is in A2:B10 (column A = meal id, column B = meal description), and the cell where the combined result is needed is, for example, D2. To return the descriptions for meal id 1 and 9 in one cell, use a formula like:

=VLOOKUP(1,$A$2:$B$10,2,FALSE) & ", " & VLOOKUP(9,$A$2:$B$10,2,FALSE)

This:

• Looks up id 1 in column A and returns its description from column B.
• Looks up id 9 in the same table and returns its description.
• Concatenates the two descriptions with a comma and space between them.

The same pattern works for any other combination of ids; just change the lookup values (1 and 9) and, if needed, the delimiter between them.

---

References:

• VLOOKUP function
• Use Excel built-in functions to find data in a table or a range of cells

Answer 3

Hi Tim

The following provides a solution based on the data you provided.

For illustration purposes, in the picture below

1. On the left, we have an Excel table named "sevents"
2. I used cells G2 downwards to enter the event ID set of values
3. In cell H2, enter the formula below and copy it down

=TEXTJOIN(" - ",TRUE,IFERROR(VLOOKUP(VALUE(MID(G2,1,1)),sevents,2,FALSE),""),IFERROR(VLOOKUP(VALUE(MID(G2,2,1)),sevents,2,FALSE),""),IFERROR(VLOOKUP(VALUE(MID(G2,3,1)),sevents,2,FALSE),""))

** I used " - " as the event delimiter in the formula.

** Adapt the formula according to your scenario

IMPORTANT NOTE:

My concern with this table setup is that you are limited to at most 10 events in the table, using digits 0 to 9.

If you enter more than 10, then you need to consider another way to enter the set of event IDs

I hope this helps you and gives a solution to your problem.

Do let me know if you need further help.

Kind regards

Jeovany CV

Answer 4

While left and right will allow you to extract the 1st and 3rd digits, you need to use mid(x,2,1) to extract the middle digit. From an ease of program maintenance point of view, it also makes sense to use mid for all 3 digits.

If your data is in A1:B9 and your dynamic criteria is in F1, then this formula will do what you describe:

=LET(data,A1:B9,look,LAMBDA(digit,VLOOKUP(VALUE(MID(F1,digit,1)),data,2)),look(1)&CHAR(10)&look(2)&CHAR(10)&look(3))

The cell containing the formula needs to be wide enough to hold the longest string in column b, tall enough for three lines, and have the Wrap text format checked