Lookup To Return Multiple Results

Mike Rini

Hi -

I've done some research, but am struggling to find the best solution on this one. I am trying to do a lookup from one sheet to another based on a Unique ID. A VLOOKUP returns only one result, but I'd like to return all of them back to one cell in the other sheet. It seems like some combination of COLLECT, JOIN, etc. might be able to work, but I'm not seeing how to do it immediately.

Question, is there a way to do this with the functions available in SmartSheets?

As always, thanks for the help.

Mike

Genevieve P.

Hi @Mike Rini

You're absolutely correct, a JOIN(COLLECT formula would be the way to go!

The way JOIN(COLLECT works is that you first list the range that has the values you want returned, and then list each range and criteria in the current, second sheet afterwards.

For example:

=JOIN(COLLECT({Column with values to return}, {Unique ID Column}, [Unique ID]@row), " - ")

The ranges in {these} are cross-sheet references to your first sheet. The value in [these] reference a cell in sheet 2, where the formula is being written. Then at the end, after closing off the COLLECT portion of the statement, I've identified how I want the values to be separated, with " - ".

You can read more about each of these functions in our Help Center: JOIN function / COLLECT function / @row function / Cross Sheet References

Let me know if you have any questions as you build this out! (Screen captures of both sheets without any sensitive data would be helpful).

Cheers,

Genevieve

Mike Rini

Thank you @Genevieve P , I feel like I'm now heading in the right direction with that help. I'm still struggling to visualize how it all comes together. I took a look at the formula examples (both yours and in the SmartSheet links you provided) - I'm not doing something right.

Here's an example of the data, the formula (clearly wrong) and the end product I'm trying to get (which is the Creative Name in Sheet 1 - 3rd column). Any help is greatly appreciated.

Genevieve P.

Hi @Mike Rini

In this example, you're missing a comma after the COLLECT function closes, but before you identify how you want the values to be separated:

), " - ")

I also notice that it looks like you want a comma between the values, instead of a - symbol. In this case, change out what's "in these".

Try this:

=JOIN(COLLECT({Sheet2_ Range 1}, {Sheet2_ Range 2}, [Unique ID]@row), ", ")

Let me know if this works! If not, are you receiving an error message?

Mike Rini

You are correct - I was missing a comma there. I do get an incorrect argument error now, though. I know I'm doing this wrong because I just keep thinking of a VLOOKUP. Sheet 2_ Range 1 is referring to the Creative Name in Sheet 2. Sheet 2_Range 2 is referring to the Unique ID in Sheet 2 - that seems wrong? Can you clarify which arguments match up against each other? In other words, which arguments in the formula represent Sheet 1 Unique ID and Sheet 2 Unique ID.

Genevieve P.

Hi @Mike Rini

No problem, I'm happy to break it down.

The first range is the one you actually want the data from. Then the rest of the formula lists columns & criteria, back and forth. You could use a JOIN(COLLECT to specify many different criteria to search through, like so:

=JOIN(COLLECT({Values you Want}, {Criteria Column 1}, "Criteria", {Criteria Column 2}, "Criteria", {Criteria Column 3}, "Criteria"), ", ")

But in your case you only have one Criteria & Criteria Column:

=JOIN(COLLECT({Sheet2_ Range 1}, {Sheet2_ Range 2}, [Unique ID]@row), ", ")

This means you are correct with your statement above:

Range 1

{Sheet2_ Range 1} = Creative Name column

Range 2

{Sheet2_ Range 2} = Unique ID column

Criteria

[Unique ID]@row = the unique value in this current row (in the same sheet where the formula is), that you're searching for in the other sheet.

I've tested on my own sheet and this exact syntax/set up should work, as long as you're referencing the correct columns. Are you still getting an error? If so, could you provide a screen capture that shows the formula actually in Smartsheet (opened up to show the colours highlighting the [unique ID] cell, etc).

Cheers,

Genevieve

Mike Rini

@Genevieve P Thank you!! I figured it out with your help. I switched from doing cross-sheet references, to doing them in-sheet - just to simplify things and figured out what I was doing wrong.

Thanks so much for the help - I think I'll be able to use this formula a lot in the future.

Mike

Genevieve P.

No problem at all! I'm glad you figured it out & understand the formula.

Cheers!

Genevieve