Combine Multiple VLookup Results into 1 cell

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.

Best Answers

  • AravindGP
    AravindGP ✭✭✭✭✭
    Answer ✓

    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.

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    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), ", "), "")

Answers

  • AravindGP
    AravindGP ✭✭✭✭✭
    Answer ✓

    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.

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

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

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    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
    Carson Penticuff ✭✭✭✭✭✭

    @AravindGP You beat me to it. 👍

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!