Searching from one sheet's range & search/match to another sheet's range for a unique identifier?

Jen Castillo

Hello,

Is it possible to search from a range in one sheet and search/match it in a different range/another sheet?

This has plagued me all day and therefore I can no longer remain clear headed about what the best course of action should be.

I have two trackers in completely different departments; QC and PROD. I want to either cell link or data mesh the progress (quantities) of specific projects in the QC tracker into the PROD tracker, but each department tracks their information differently. I have no consistent unique identifier between the two.

For example:

A is shipping, B is a work order, C is a purchase number, D is a financial number.

QC has columns A / B / C / D but only 1 of the 4 will be used. They usually use B / C but not always.

PROD also has A / B / C / D and mostly uses C / D but can be flexible to add A / B.

D is the most consistent, but unfortunately it is more overall project finance-related. (It gets used for multiple sizes of the same item so there might be repeats.) So if we were to go off D, we would need another value in A / B / C to confirm that it is the right item.

I've done a couple join/collect formulas to try and pull the first value from a range in both sheets as my "identifier" but due to the nature of the different sheets, sometimes it pulls A on the QC but then C on the PROD sheet, or any other combination.

I can make minor updates to either sheet, like adding a helper column, but I cannot change their process or have them use one specific identifier. (QC focuses on moving product, PROD focuses on financials.)

I am hoping that leaving work for the day and going to bed will bring me clarity, or perhaps some kind soul out there will point out the solution that's right under my nose.

In a nutshell: It is like I'm trying to arrange a marriage between people that do not speak the same language, so I am open to any suggestions!

Thank you! 😵

Paul Newcome

My suggestion would be a helper column on both sheets that combines the various columns into a single string in a single column.

=JOIN(COLLECT([Shipping Label (A)]@row:[Financial Code (D)]@row, [Shipping Label (A)]@row:[Financial Code (D)]@row, @cell <> ""), "-")

Using the same formula on both sheets should provide a single column with a truly unique value on every row which can then be used in a straightforward INDEX/MATCH.

=INDEX({Column To Pull Over}, MATCH([Helper Column]@row, {Helper Column}, 0))

Paul Newcome

Are you able to provide some screenshots for context?

Jen Castillo

Yes!

For my QC grid:

From PROD

QTY Delivered/QTY Approved (orange/green columns) are the data I'd like to carry to the PROD tracker. Bolded are my possible identifiers.

QC will use one of A / B / C / D as their unique identifier. PROD will always have D and usually C or B. PROD technically goes by the Financial Code (D) as they are tracking the full item (and all sizes)'s production progress as opposed to QC tracking how many deliveries arrive/how many are acceptable.

So in red, the item is the same "style" but different sizes. The Item Sku is different, Purchase (C) is different because different delivery shipments, but Financial Code (D) is the same as they are the same style.

In yellow, the Item Sku is the same, Financial Code (D) is the same, but Purchase (C) is different because different delivery dates.

(I forgot to do this, but on my gray lines there are formulas summing up from the total expected order QTY vs what has actually been approved vs what is available.) The PROD grid has many more columns about vendors, clients, and etc for specific projects. I originally tried to use Item Sku but the QC grid has hundreds of entries, some for projects and some not; their operation does not track who the item is inevitably for. We do not want to get project money/materials mixed with replenishment. For example, we are supporting project AB but there is also a replenishment order for a regular client. We do not want to count the product for our regular client as part of project or vice versa.

Paul Newcome

My suggestion would be a helper column on both sheets that combines the various columns into a single string in a single column.

=JOIN(COLLECT([Shipping Label (A)]@row:[Financial Code (D)]@row, [Shipping Label (A)]@row:[Financial Code (D)]@row, @cell <> ""), "-")

Using the same formula on both sheets should provide a single column with a truly unique value on every row which can then be used in a straightforward INDEX/MATCH.

=INDEX({Column To Pull Over}, MATCH([Helper Column]@row, {Helper Column}, 0))

Jen Castillo

Hi @Paul Newcome

Your helper columns worked! The data is sometimes inconsistent between the two sheets so I used CONTAINS instead and that worked!

=INDEX(COLLECT({QC Approved QTY}, {QC Helper}, CONTAINS(@cell, HELPER@row)), 1)

My last two hurdles are:

-How would I update the INDEX(COLLECT formula above to ignore blanks/get rid of #INVALID VALUE errors? All my attempts have populated #INCORRECTARGUMENT or other errors.

-Is there any way to combine quantities if there are multiple rows that meet the same criteria? For example:

QC

PROD

Thank you so much!

Paul Newcome

You would use a SUMIFS to combine quantities. You can use this even where there is only one as well.

Jen Castillo

EDIT: actually, nevermind. Thank you!

Paul Newcome

Happy to help. 👍️