Index Match with multiple ranges?

Options

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!

Tags:

Best Answer

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭
    Answer ✓
    Options

    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.


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

Answers

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭
    Answer ✓
    Options

    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.


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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!