Like peanut butter and jelly, INDEX and MATCH are two Excel functions that work better together. We've already covered the more common Excel reference functions on this blog: VLOOKUP and HLOOKUP. INDEX and MATCH, when combined, form what may be the most versatile Excel reference method of all.
The INDEX function is incredibly simple. Give Excel an array (a section of your spreadsheet), then identify a column and a row within that array. The function will return the cell in that column and row. That's it.
In this example, we’ll attempt to find the Measles cases in County 3. The function we’ll end up using is:
=INDEX(B4:E7, 2, 3)
Step 1. Identify an array
The first argument is the array or search area we’ll be pulling data from, excluding the label column and rows we included for VLOOKUP and HLOOKUP. In our function, we use B4:E7 as our array.
Step 2: Identify the row.
The second argument is row_num or the row we’re searching for. In the example, we’re looking for Measles, so we specify row 2. Note that this number is relative—you are specifying the second row from the top of the array, not row 2 on the spreadsheet.
Step 3: Identify the column (if your array includes more than one).
The third argument col_num, is the column of the array we’d like to reference. It is an optional argument, which we’ll use in our example; we enter 3 so we can reference County 3. (This number is also relative to the array, not for the whole spreadsheet). This completes our function and returns an answer of 3 Measles cases in County 3.
The fourth INDEX argument, area_num, is an optional range within the array we’d like to target with our preceding arguments. For the example, our array is rather small and doesn’t need this argument, so we opt not to include it.
The MATCH function is useful because it enables you to search for any word or phrase in your spreadsheet. It essentially locates this word or phrase in a column. In this example, we’ll attempt to find the position of "Noro" in our disease column. To do so, we’ll need the function below, which has 3 arguments.
Step 1. Input the search key.
The first argument is the search_key, which is the label or value our function will search for. Since we’re searching for Noro, we enter this into our function with quotation marks.
Step 2: Specify the range (array).
The second argument is the range, which is the array we want to search in. We want to find Noro’s position in the disease column, so we choose an array covering only A4:A7.
Step 3 (optional): Specify the search type
The last optional argument is [search_type], which specifies the method for the first argument. If the third argument isn’t included, it is assumed to be 1; if match_type is 1, the function assumes the array is sorted by ascending order and returns the largest value equal to or less than the search_key. If search_type is 0, MATCH finds an exact value to search_key. If we enter -1, MATCH finds the smallest value that is greater than or equal to the lookup_value. In the example, we enter zero to finish our function and get a position of 1, the position of Noro in our disease column.
Nesting Match and Index
By placing the MATCH function inside the INDEX function, you can create a search mechanism that's even more powerful. For example,
=INDEX(B4:B7, MATCH("Noro"A4:17, 0))
This function instructs Excel to search for the number of Noro cases in County 1. Excel first identifies the row that contains the search key "Noro," then uses that result as an input to the Index function. By creatively combining these functions, you can easily get a handle on data in even the largest and most complex spreadsheets.