Formula Help

michellelkirkland

Hi All - I am hoping someone might be able to help me. I am terrible at formulas and I cant seem to figure out how to pull what I need on my own so I copied a formula from another sheet we have at our company. The issue is that it returns it multiple times. So the expected result is "four", the formula returns "four" anytime the condition is met.

Here is the formula I am using:

=JOIN(COLLECT({Different Sheet (2024) Range 6}, {Different Sheet (2024) Range 1}, CONTAINS([Product (P)]@row, @cell)), "")

Even after doing some reading I am still not quite sure what Join and Collect do and maybe there is a different formula altogether that i can do. My goal is that I can find what company manufactured a specific batch. Range 6 in this case is where the companies are listed. Range 1 is where the batch numbers are.

Any help would be greatly appreciated!!

Paul Newcome

Are you able to provide some screenshots for context?

michellelkirkland

Thank you! I hope the information below is helpful.

This is one of the results that I am getting. It is as if each time if finds the batch number it reports the data that is in range 6 but I just want it to show once.

Range 1 is

Range 6 is

Range 1 and 6 are referring to different sheets.

Product (P) is a column in the current sheet.

The goal is to look for the same lot number in "Product (P)", find the same one in "Range 1" of the other sheet, and return what "Strain" is listed in "Range 6" for that lot number.

Paul Newcome

Give this a try:

=JOIN(DISTINCT(COLLECT({Different Sheet (2024) Range 6}, {Different Sheet (2024) Range 1}, CONTAINS([Product (P)]@row, @cell))), "")

michellelkirkland

That did it!! Thank you so much!

Paul Newcome

Happy to help. 👍️