Return Multiple Values with Index/Match or VLOOKUP

Michael Ciccone

Hi all,

I'm working to develop a system where Smartsheets will collect all matching names (First Last in the same cell) and list them together based on what company they work for (listed in an adjacent cell).

INDEX/MATCH shows promise, I've used it before as an array formula in Excel however in Smartsheets I cannot figure out how to get it to return anything but the first found value as I drag the formula down. Any ideas/alternatives? I'd prefer not to use reports, this is going to be a template and I'd like it to be as flexible as possible, and have the criteria based on data within the sheet itself.

For an added level of complexity of course I'm referencing data in another sheet.

Here's what I have written so far:

=INDEX({Executive Summary Range 7}, MATCH([Column2]$1, {Executive Summary Range 8}), 0)

Is there a way to use a helper column with unique number identifiers in conjunction with the INDEX/MATCH? I'm at a loss. Thanks in advance!

Paul Newcome

Take a look at a JOIN/COLLECT. It will allow you to pull multiple values based on the criteria as opposed to the first found with an INDEX function.