Find and Return

Amber Jackson

Good morning gurus!

I need some magic to find a return all instances of information in an external sheet. For example, I need to find all instances of "ABC" in the Material Name, and return the associated Batch number. All the formulas I try will give errors or only give me the data for one instance. Any ideas? Thanks in advance!

Genevieve P.

Hi @Amber Jackson

You could use a JOIN(COLLECT formula to JOIN together all the Batch Numbers in one cell if they meet the criteria you specify in the COLLECT Function.

Try something like this:

=JOIN(COLLECT({Batch Column}, {Material Column}, CONTAINS("ABC", @cell)), ", ")

The ", " at the end of the function identifies how you want your values to be separated. You could change this to something else, like a " - " or " / " if you prefer. You could also use CHAR(10) to create a line break and put the formula into a Multi-Select cell so they appear as separate values, as well:

=JOIN(COLLECT({Batch Column}, {Material Column}, CONTAINS("ABC", @cell)), CHAR(10))

Let me know if this works for you!

Cheers,

Genevieve

Amber Jackson

@Genevieve P. Do you know of a way to collect all of the batch numbers on different rows instead of in one cell? I basically need to display all of the rows that meet the criteria, almost like a report.

Thoughts?

Genevieve P.

Hi @Amber Jackson

Do you have any other unique identifier on the source sheet, so that the formula can recognize each row individually and bring it back?

Otherwise, I would recommend using a Report in this instance, since it will display your data row-by-row.

Amber Jackson

@Genevieve P.

The material name is really the only identifier I have. I have many more columns to display and columns to add (that are unique to the material name) so a report just won't work. I'm stuck 😕

Genevieve P.

Hi @Amber Jackson

Thank you for clarifying that it needs to be in another sheet. There isn't a way to automatically parse out the values that you're collecting, a formula can only either combine it into one cell, or bring back individual values based on the criteria you're looking for.

I would suggest manually copy/pasting the Material number between sheets; you could set up an Alert when a new row is added to the source sheet so you know there's a new Material number that should be pasted in to this second sheet. Then all of your other columns and formulas will be based off of this one unique identifier and can bring back information from that one specific row in the source sheet.

Cheers,

Genevieve