Index Match with multiple ranges?

SteveC

Hi All,

I am stumped on a formula and hoping for some direction. I am attempting to put together an Index(match) that will bring in the "order number" from sheet two to the "Assigned to Contract" column, if the unique identifier appears in any of the "Unique ID columns" (Columns in between hidden for better viewing).

Is there a way to do an Index Match with multiple ranges?

Thanks in Advance!

MichaelTCA

Hello,

Try out this function:

=IF(ISBLANK([Unique ID]@row),"",INDEX(range of values you want, MATCH([Unique ID]@row,column to evaluate,0)))

INDEX(MATCH()) when you have 1 criteria and dynamic column positions.

Otherwise:

INDEX(COLLECT()) when you have multiple criteria, dynamic column positions, or to return a non-first position"

If you use multiple columns within the range of values you want, you will need to specify which column to pull data from at the end of the INDEX function.

https://help.smartsheet.com/function/collect

INDEX(COLLECT()) is similar to SUMIFS(). It might look intimidating at first, but it's simple for an advanced function.

MichaelTCA

Hello,

Try out this function:

=IF(ISBLANK([Unique ID]@row),"",INDEX(range of values you want, MATCH([Unique ID]@row,column to evaluate,0)))

INDEX(MATCH()) when you have 1 criteria and dynamic column positions.

Otherwise:

INDEX(COLLECT()) when you have multiple criteria, dynamic column positions, or to return a non-first position"

If you use multiple columns within the range of values you want, you will need to specify which column to pull data from at the end of the INDEX function.

https://help.smartsheet.com/function/collect

INDEX(COLLECT()) is similar to SUMIFS(). It might look intimidating at first, but it's simple for an advanced function.