What is the syntax for INDEX/MATCH from ranges on two Sheets?

I am trying to match data from two different Sheets into a third Sheet. I know vlookup will not work for this, so I am trying my hand at index/match and can't get the syntax right. How do you write a formula that can return results from ranges on two different Sheets?

Answers

  • Syntax for a Match function:

    MATCH( search_value, range, [ search_type ])

    • search_value — The value to search for.
    • range — The cell range (lookup table) to be searched.
    • search_type —[optional] The default is 1. The manner in which to search, depending on whether the range is sorted ascending (1), not sorted (0), or sorted descending (-1).

    Syntax for Index function:

    INDEX( range, row_index, [ column_index ])

    • range — The group of cells that you want to evaluate
    • row_index — The row position (used in a one-dimensional collection such as a list) of the item to return
    • column_index —[optional] The column position (used in a two-dimensional collection such as a table) of the item to return

    The range variable can be a {{cross sheet reference}}. Does this help? As far as I can tell, each function only accepts a single range value. If you need to looking up values from two different cross sheet references then you'd need to write separate functions and find another way to accomplish your goal.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Kelsey K

    To add to @SolutionSal's explanation, there are a few other threads in the Community with examples of how to look into multiple sheets that you may find helpful:

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!