Copy values from multiple columns if condition exists

I'm trying to figure out how to use VLOOKUP (or something else) from another sheet to lookup a "Shipped Part Number" and return all of the "Component Part Numbers" from the columns in the sheet attached. Any help would be greatly appreciated.

I can make it work manually modifying the VLOOKUP by incrementing the column number, but I'd like to make this a column formula so it keeps looking until it doesn't find anything.

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @LakeWaconia

    Based on your sheet set-up, what I would do is add a helper column in the source sheet to bring together all of the Components into one cell for that row:

    =JOIN(COLLECT([Component 1]@row:[Component 11]@row, [Component 1]@row:[Component 11]@row, ISTEXT(@cell)), CHAR(10))

    I've put mine in a Multi Select column, separating the values with a return or CHAR(10). Note that I used the COLLECT function to only return values in that row that are being read as text so it wouldn't return the quantity.


    Then you can do a simple INDEX(MATCH to Match the Shipped Part Number across sheets and return the values in this helper column into another Multi-Select column:

    =INDEX({Total Components Column}, MATCH([Shipped Part Number]@row, {Shipped Part Number Column}, 0))

    Let me know if this would work for you!

    See: JOIN Function / COLLECT Function / INDEX Function / MATCH Function

    Cheers,

    Genevieve

  • LakeWaconia
    LakeWaconia ✭✭✭

    Genevieve,

    That worked absolutely perfect. Thank you so much!

  • Genevieve P.
    Genevieve P. Employee Admin

    Wonderful, I'm glad I could help! 🙂

  • LakeWaconia
    LakeWaconia ✭✭✭

    Genevieve,

    Any ideas on if I wanted to collect those same items, but instead of joining them into one cell, listing them separately in cells, i.e.

    Row 1: 250P

    Row 2: 250PC

    Row 3: GT34BP

    etc....

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @LakeWaconia

    There currently isn't a way to gather information cross-sheet with a formula and parse out details like this down rows with one criteria bringing back multiple values, it can only collect the data from one criteria into one cell.

    That said, if you identify on each row both the Part Number and the Component Number in two columns, then we could set up your source sheet to repeat the column names in the very top row.

    If we do that, you could have a cross-sheet INDEX(MATCH(MATCH which first finds a match based on the Part Number and then secondly looks at the top row to find a match for the Component Number.

    For example:

    =INDEX({Component Range}, MATCH([Shipped Part Number]@row, {Part Number}, 0), MATCH([Component Name]@row, {Row 1}, 0))


    The {Component Range} would a range that starts from Component 1 column and stretches all the way to Component 11. Then your {Row 1} range would need to start from the same column as your Component Range, but only looking at the top row in the sheet. Does that make sense?

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!