Return Multiple Values with Index/Match or VLOOKUP

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!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!