Microsoft Excel Match Index Functions

/ Uncategorized / By

Use Match Index instead of VLookup

Our Microsoft Excel training courses take your through many of the program’s built-in functions, including the combined use of Match Index. One of the most popular is the VLOOKUP function, which lets you search for and return values in an Excel worksheet as if it were a database table.

VLOOKUP is a powerful and much-used Excel function. It finds a lookup value in a table and returns the corresponding value of another column. This is great for looking up information by reference.  If you are a power Excel user, you are likely very familiar with this function. If not, check out this Error! Hyperlink reference not valid.:

There are a few limitations with VLookup…

  1. Your data range is limited to a table.
    • That means the data you are looking up has to be in a standard tabular form. You cannot use VLOOKUP to find a lookup value in a different table, sheet, or offset row. This limits the ways you can display your data, as anything you want to lookup must be available in a standard table format in your spreadsheet.
  2. VLOOKUP always searches the leftmost column of the specified table to find the lookup value.
    • Again, this limits your choices in presenting data as lookup values always have to be to the left of the return values. This sometimes means you must have multiple copies of tables in order, think far ahead when creating tables that might be used in lookup, or reorder columns after the fact simply to use VLOOKUP.
  3. You can only specify the return value column by index number.
    • This means there is no way to include a static reference to the return value column. If someone adds a column between the lookup value column and return value column, it will break your VLOOKUP and you have to manually increase the column index number in the formulas. This is a maintenance nightmare.
  4. VLOOKUP provides a very limited approximate match feature.
    • The only aproximate match option finds the nearest “less than” value. Unless you want that type of behavior, you’re out of luck and can’t use it.
  5. VLOOKUP slows down big spreadsheets.
    • When the value you are looking up is in one column, and the value you want to return is in the 30th column in the table, VLOOKUP requires you to select all 30 columns. This results in a lot of extra computations and can bring large spreadsheets to their knees.
    • With MATCH INDEX , you select the column containing the lookup value and the column containing the return value, and nothing else. That makes MATCH INDEX much more efficient.

The answer to these problems and limitations is the MATCH INDEX lookup method. This methods uses two functions together to provide a more safe and flexible lookup feature. Here’s how each function works, independently:

  • INDEX returns the value at the intersection of a row and column in a given range.
    • Formula: =INDEX(Array, Row_num, Column_num)
      • Array – The range of cells
      • Row_num – The row to return data from
      • Column_num – The column to return the data from [optional]
  • MATCH returns a position of an item in an array that matches a value.
    • Formula: =MATCH(Lookup_value, Lookup_array, Match_type)
      • Lookup_value – The value you want to find in the lookup value array
      • Lookup_array – The range containing lookup values
      • Match_type – Exact (0), Nearest Less Than (-1), or Nearest Greater Than (1) [optional]

Using Match Index

Combining the two functions, you are able to overcome the limitations and downsides of VLOOKUP.

You can build a lookup that allows you to specify the lookup column and return value column completely independently, and also control of the nature of the approximate match, if not exact.

The arrays are ranges and you can specific Column_num in place of Row_num, which mean you are not limited to using columns only; this can be used in place of HLOOKUP.

It still has the approximate match feature as nearest “less than” by default, and carries the same sort warnings as VLOOKUP.

Here’s how the combined function works:

=INDEX(Return_value_range, MATCH(Lookup_value, Lookup_value_range, Match_type))

  • Return_value_range – The range that holds the return values
  • Lookup_value – The value you want to find in the lookup value array
  • Lookup_value_range – The range containing lookup values
  • Match_type – Exact (0), Nearest Greater Than (-1), or Nearest Less Than (1)

Recap of what the INDEX function does.

The INDEX function has a very simple job – look in a column of data and return a value from the row that you specify.

Here’s an example of the INDEX function in action:

=INDEX(A2:A8,3)

Recap of what the MATCH function does

The MATCH function also has a simple job – look in a column of data and return the row number of a value that you specify.

Here’s an example of the MATCH function in action. Let’s assume we have a column of names, and we want to know which row the word Microsoft is found in. We’ll assume that each name appears only once in the list – this will make things simple for us.

=MATCH(“Microsoft”,B2:B8,0)

Example: Data in Columns

Option 1

  1. Type the following data into a new worksheet:
2.     A1: Part   B1:  Code   C1:  Price   D1:  Find Part  E1:  Find Code
3.     A2: x      B2:  11     C2:  5.00    D2:  y          E2:  12
4.     A3: x      B3:  12     C3:  6.00    D3:  y          E3:  11
5.     A4: y      B4:  11     C4:  7.00    D4:  x          E4:  12
6.     A5: y      B5:  12     C5:  8.00    D5:  x          E5:  11
  1. To retrieve the price for part y with code 12 and return the value to cell F2, type the following formula in cell F2:

=INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0))

  1. Press CTRL+SHIFT+ENTER to enter the formula as an array formula.
    The formula returns the value 8.00.
  2. Select cell F2, grab the fill handle, and then fill down to cell F5 to retrieve the price for each part and code combination.

Option 2

A second method yields the same results but uses concatenation instead. The following sample formula may be better for matching data against more than two criteria because it does not require nested IF statements. This method is identical to Method 1 except that you replace the formula in step 3 with the following formula:

=INDEX($C$2:$C$5,MATCH(D2&E2,$A$2:$A$5&$B$2:$B$5,0))

Example: Data Arranged in Rows

Option 1

  1. Type the following data into a new worksheet:
2.     A1: Part        B1: x      C1: x     D1: y       E1: y
3.     A2: Code        B2: 11     C2: 12    D2: 11      E2: 12
4.     A3: Price       B3: 5.00   C3: 6.00  D3: 7.00    E3: 8.00
5.     A4: Find Part   B4: y      C4: y     D4: x       E4: x
6.     A5: Find Code   B5: 12     C5: 11    D5: 12      E5: 11
  1. To retrieve the price for part y with code 12 and return the value to cell B6, type the following formula in cell B6:

=INDEX($B$3:$E$3,MATCH(B4,IF($B$2:$E$2=B5,$B$1:$E$1),0))

  1. Press CTRL+SHIFT+ENTER to enter the formula as an array formula.
    The formula returns the value 8.00.
  2. Select cell B6, grab the fill handle, and then fill right to cell E6 to retrieve the price for each part and code combination.

Option 2

A second method yields the same results but uses concatenation instead. The following sample formula may be better for matching data against more than two criteria because it does not require nested IF statements. This method is identical to Method 1 (under Example 2) except that you replace the formula in step 3 with the following formula:

=INDEX($B$3:$E$3,MATCH(B4&B5,$B$1:$E$1&$B$2:$E$2,0))