JOIN COLLECT MATCH Database

Michelle Maas

I am pulling data into a separate sheet (database) from a referenced sheet "Pipeline". In the Pipeline there are two columns, Index and Acronym. In the database I want these two column row data to JOIN together in a column named "Project Number" by recognising the same Index number in the Database and matching it to the Index number in the Pipeline, i.e. Pipeline column "Index" is 000001 and Pipeline column "Acronym" is ABC = auto-populates Database column "Project Number" with 000001 ABC. I've been playing with the below formulas but I am getting #INCORRECT ARGUMENT SET:

=MATCH(Index@row, Index@row, JOIN(COLLECT({Archive Index}, {Archive Acronym}, {Archive Index}, 1)))

OR

=JOIN(COLLECT({Archive Index}, {Archive Acronym}, MATCH(Index@row, Index@row, {Archive Index}, 1)))

Paul Newcome

Try something like this:

=Index@row + " " + INDEX({Pipeline Column}, MATCH(Index@row, {Index Column}, 0))

bisaacs

Hey @Michelle Maas,

Something like this might work:

=INDEX(COLLECT(Archive:Archive, Index:Index, Index@row), 1) + " " + Acronym@row

Michelle Maas

I tried the below formulas and got #INCORRECT ARGUMENT SET:

=INDEX(COLLECT({Archive Index}, MATCH(Index@row), 1) + " " + {Archive Acronym})

Then tried the below formula which worked great:

=Index@row + " " + INDEX({Archive Acronym}, MATCH(Index@row, {Archive Index}, 0))

Now I need to add an additional referenced sheet with has the same columns. The one previously referred to is the Archived Sheet and now I want to add the Current Pipeline. How do I do this?

Also, I have a helper row for identifying the Parent Row, how do I add this to the formula to only pull data from each Parent i.e. {Pipeline Level}, "1").

Paul Newcome

How would the new sheet be incorporated? Would the acronym only be in one sheet or the other?

To incorporate another range/criteria set to filter by, we would need to switch over to an INDEX/COLLECT like so:

=Index@row + " " + INDEX(COLLECT({Archive Acronym}, {Archive Index}, @cell = Index@row, {Pipeline Level}, @cell = "1"), 1)

Depending on how exactly you are populating the {Pipeline Level} range, you may need to remove the quotes from around "1".

Michelle Maas

@Paul Newcome To clarify, the Pipeline is the active sheet where we record incoming enquiries. The formulas in the reporting sheet match the Index to pull data from the Pipeline. The Archive sheet is where we move the closed sales, with the same columns as the Pipeline as it is an archiving sheet. I need to pull data from both the Pipeline and the Archive to show in the Reporting sheet what is active and what has been closed, via matching the Index number and formulating the index number and acronym into one column 'Project Number' in the reporting sheet, and populating the other columns such as client name, opportunity, etc., which are all the same formula matching their respective column in each referenced sheet to add it to the relevant field in the Reporting sheet. The only rows I want the formulas to pull data to the Reporting sheet from the Pipeline and the Archive are Level 1 (parent row). I managed to fix the formulas:

For the project number column:

=Index@row + " " + IFERROR(INDEX({Pipeline Acronym}, MATCH(Index@row, {Pipeline Index}, 0)), INDEX({Archive Acronym}, MATCH(Index@row, {Archive Index}, 0)))

and for the other columns:

=IFERROR(INDEX({Pipeline Client}, MATCH(Index@row, {Pipeline Index}, 0)), INDEX({Archive Client}, MATCH(Index@row, {Archive Index}, 0)))

Thanks for helping me to get there.😁