How Does Smartsheet Handle Duplicates in INDEX/MATCH? Sorting for Accurate Results

System

This discussion was created from comments split from: INDEX and MATCH across two sheets: a detailed explanation.

Danielle Arteaga

Hi, Haley. INDEX/MATCH will return the FIRST match that is found. If you have multiple matches, you'd be better off using a JOIN/COLLECT formula.

=JOIN(COLLECT({Active Award Data Range 1}, {Active Award Data Range 2}, [Active SIT awards]@row), CHAR(10))

A couple of notes: You may need to swap Active Award Data Range 1 and Active Award Data Range 2 in this formula. Not sure, because I don't know how you've defined those ranges. But just a heads up as a possible troubleshoot if this does not work as written.

Also, the CHAR(10) at the end forces each result onto its own line in the cell. You will want to wrap the text in those cells / that column. But, if you don't want this, the CHAR(10) function is optional - just delete.