How to get a lookup (Vlookup or Index Match) to return multiple values?

Heather Walker

Hi @Paul Newcome -

I have (I think) a similar need but I am way over my head with the JOIN(COLLECT) inside of a VLOOKUP.

I have a Brand column that is a multi select dropdown.

Based on the Brand column, I'd like to return links to each specified logo using a lookup table on a separate sheet (where I can update links later, add more brands and links as needed, etc.)

So far, I've got the VLOOKUP working with this:

=VLOOKUP(Brand@row, {Brand Lookup Table Range 1}, 2)

But it is of course only returning one value.

How do I add a JOIN(COLLECT()) to the VLOOKUP if it's referencing a different sheet?

Thanks for any info!

Paul Newcome

@Heather Walker I'm not sure I follow.

Are you trying to return "unbranded specs link" and "unbranded specs link 2" from the second screenshot using a single formula based on the multi-select dropdown in the first screenshot?

Heather Walker

@Paul Newcome

Yes, if it's possible to do with one formula. Is it? Is there another way to do it?

I have potentially 20+ choices for Brand, each with a different logo to be used.

Paul Newcome

Try something like this...

=JOIN(COLLECT(Other Sheet Link Column}, {Other Sheet Brand Column}, HAS(Brand@row, @cell)), CHAR(10))

And then enable text wrapping.

Heather Walker

@Paul Newcome Thanks so much, that worked perfectly!

Paul Newcome

Happy to help. 👍️

Rob Wright

I'd like to piggy back this one, a bit late to the party I know. I have tried to follow Tamara's example but I think some of the info assumes knowledge anyone not aware of that specific problem might . @Paul Newcome can you help?

I have a sheet I want to look up from. In another sheet, I basically want to return all corresponding results from the table for a given "project reference" which will have multiple rows in the lookup sheet, in separate rows.

Treat me as a total newbie; how do I do this across sheets?

Paul Newcome

@Rob Wright Are you able to provide some screenshots for reference?

Rob Wright

sorry Paul i didn't realise you had responded!

I have the sheet below I am creating currently

Based on a column (project reference) which is further to the left and takes the form d0001, I want to pull all corresponding results from the separate sheet (see below)

so for instance if I put d0001 into the lookup cell on the first sheet, in the risk description column I would want to pull all results from the risk/issue column in the second sheet where the corresponding project reference was also d0001.

So a vlookup/index and match type deal, pulling multiple results from one sheet into another...

Paul Newcome

@Rob Wright INDEX and VLOOKUP will only ever pull a single result. If you are wanting to pull multiple results, you want to look into the JOIN function. Since there is no "JOINIF" function, we would incorporate the COLLECT function so that we can specify the range/criteria set(s) of which to join. We would end up with somethign that looks like this...

=JOIN(COLLECT({Other Sheet Risk Column}, {Other Sheet Proj ID Column}, [Project ID]@row), "delimiter of choice")

Rob Wright

Thanks for the assistance Paul. It seems like this is possible in Excel, but not in Smartsheet with its limitations. Given it cannot be done, I am going to use your method, as it will be exported to Excel anyway, where I will use the text to columns wizard, using the delimiter named in the above smartsheet formula

Paul Newcome

@Rob Wright If you are wanting it parsed down rows, have you looked into just creating a report?