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.

Haley Gehris

When using an INDEX/MATCH formula, if the lookup value is listed twice on the source sheet, how does Smartsheet determine which value to return? Is it the first one it comes to? Is it looking at the source sheet from top to bottom, left to right?

In my case, the " [Active SIT awards]@row " is duplicated on the source sheet "Active Award Data Range", and I am wondering if there is a way to sort the source sheet so that Smartsheet picks up the correct data?

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.