Copy values from multiple columns if condition exists

LakeWaconia

Grip-Tite Stamping BOM.xlsx

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.

Genevieve P.

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

Genevieve,

That worked absolutely perfect. Thank you so much!

Genevieve P.

Wonderful, I'm glad I could help! 🙂

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.

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