Searching from one sheet's range & search/match to another sheet's range for a unique identifier?
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.
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! 😵
Help Article Resources
Check out the Formula Handbook template!