Outputting multiple VLookup values in Excel

Question

I'm looking to create a formula which outputs numerous values associated with an ID into just one cell. The number of values varies e.g. one ID may have 3 values associated with it, another may have just 1.

I know that if I were using VBA, I could use a loop to output all the values but I'm using just Excel in this example. All I have so far is a simple lookup which outputs just one value

=VLOOKUP(B2,'Customer Tape'!A:B,2,0)

(B2 is the cell; for Customer Tape column A is the ID, column B is the value)

Is there any way to get all of the values associated with the ID (e.g. values on other rows) and output it to the same cell?

Which version of Excel do you have/need to support? – Rory Jul 22 '21 at 14:20 — Rory, Jul 22 '21 at 14:20
You'll need code then, if it has to be a formula. – Rory Jul 22 '21 at 14:36 — Rory, Jul 22 '21 at 14:36

Scott Craner · Accepted Answer · 2021-07-22T15:06:33.167

3

With older versions it is either vba or a helper column.

With the helper column put this in C2 and copy down:

=B2&IFERROR(","&VLOOKUP(A2,A3:C$1040000,3,FALSE),"")

Then you can use a normal VLOOKUP to return just the first result from column C:

=VLOOKUP(E2,A:C,3,FALSE)

If you want a vba version then see here:

EXCEL return range of values based on criteria

or here:

Merge values of column B based on common values on column A

Or here:

TextJoin UDF For Excel 2013

For Office 365 Excel no helper column needed:

=TEXTJOIN(",",TRUE,FILTER(B:B,A:A=E2,""))

edited Jul 22 '21 at 15:06

answered Jul 22 '21 at 14:51

Scott Craner

148,073
10
49
81

Thanks Scott. Although I'm entering your formula and only getting one value - even though there are clearly more identical values in the next few lines. – Danny Jul 22 '21 at 15:05
It will only show one value till you fill the whole column with the formula. It is dependent on the output of the formula below. – Scott Craner Jul 22 '21 at 15:07
Yes I've dragged the formula down and still only getting the one value per ID. – Danny Jul 22 '21 at 15:08
1

please post a link to a picture of the sheet showing the formula in the first cell. As you can see it works for me. – Scott Craner Jul 22 '21 at 15:09
Sure, question updated to include columns A, B & C, followed by the formula for Cell C2. – Danny Jul 22 '21 at 15:12
I need to see the column letters and row numbers and the cell in which the formula is put or the formula means nothing. – Scott Craner Jul 22 '21 at 15:17
Updated to include the above – Danny Jul 22 '21 at 15:19
1

`B$2:C$88` should be `A3:C$88` – Scott Craner Jul 22 '21 at 15:21

Outputting multiple VLookup values in Excel

1 Answers1

Linked