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

Kelsey K

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?

SolutionSal

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.

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:

• Double Index Match
• Index Match using two sheets
• INDEX and MATCH across two sheets: a detailed explanation (this is to explain the single Index(Match structure)

Cheers,

Genevieve