Combine Multiple VLookup Results into 1 cell

TomM

Hi,

I am wanting to display multiple vlookup results from a separate sheet into 1 cell.

Example:

Cabling Allocation Sheet, where we input an allocation to a text name "ECC" which can be multiple cables in the same run.

Requirements Sheet - Need the output to be "115, 116, 117" (I have manually written them in at the moment)

I can get the number to appear if there is a single value using the following formula, (however it only lists 1 cable id if multiple)

=IFERROR(VLOOKUP(Usage@row, {Report Range 4}, 2, false), "")

Is there a formula which can do this?

Many thanks in advance.

AravindGP

Hi @TomM

You can use this formula instead. =JOIN(COLLECT({Cable ID reference}, {Fibre Allocation reference}, Usage@row), " , ")

In this formula, {Cable ID reference} refers to the cross-sheet reference you create in your Requirements Sheet from your Cable Allocation Sheet by selecting the Cable ID column. You can reference the column by clicking on "Reference another sheet" while writing the formula and selecting the Cable ID column. {Fibre Allocation reference} refers to the cross-sheet reference you create in your Requirements Sheet from your Cable Allocation Sheet by selecting the Fibre Allocation column.

Carson Penticuff

Instead of VLOOKUP, I would recommend a Join(COLLECT()) for this use case. You would need to setup cross sheet references for the Cable ID and Fibre Allocation columns, but I personally try to avoid VLOOKUPS when possible.

If you are not tied to the VLOOKUP approach, give this a try:

=IFERROR(JOIN(COLLECT({Cable ID}, {Fibre Allocation}, Usage@row), ", "), "")

AravindGP

Hi @TomM

You can use this formula instead. =JOIN(COLLECT({Cable ID reference}, {Fibre Allocation reference}, Usage@row), " , ")

In this formula, {Cable ID reference} refers to the cross-sheet reference you create in your Requirements Sheet from your Cable Allocation Sheet by selecting the Cable ID column. You can reference the column by clicking on "Reference another sheet" while writing the formula and selecting the Cable ID column. {Fibre Allocation reference} refers to the cross-sheet reference you create in your Requirements Sheet from your Cable Allocation Sheet by selecting the Fibre Allocation column.

TomM

Thanks for the very quick reply @AravindGP , that worked perfectly.

Carson Penticuff

Instead of VLOOKUP, I would recommend a Join(COLLECT()) for this use case. You would need to setup cross sheet references for the Cable ID and Fibre Allocation columns, but I personally try to avoid VLOOKUPS when possible.

If you are not tied to the VLOOKUP approach, give this a try:

=IFERROR(JOIN(COLLECT({Cable ID}, {Fibre Allocation}, Usage@row), ", "), "")

Carson Penticuff

@AravindGP You beat me to it. 👍