ThisCalculator

Everyday

Two Way Lookup with INDEX and MATCH Approximate

Updated · Standard formula, shown below

The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.

This calculator performs a two-way lookup using the INDEX and MATCH functions with approximate matching, returning the value at the intersection of a specified row and column in a table. You enter the table values, the row lookup value, the column lookup value, and whether you want an approximate or exact match, and the calculator returns the corresponding table entry. It is designed for Excel and Google Sheets users who need to understand how approximate matching works in two-dimensional lookups.

Formula

The two-way lookup formula combines two MATCH functions to find the row and column positions, and an INDEX function to return the value at that intersection. For approximate matching, MATCH finds the largest value in the lookup array that is less than or equal to the lookup value, assuming the array is sorted in ascending order.

In general form: =INDEX(table, MATCH(row_lookup, row_headers, match_type), MATCH(col_lookup, col_headers, match_type))

  • table: the range of cells containing the data to retrieve (e.g., B2:D10).
  • row_lookup: the value you are searching for in the row headers (e.g., a product ID).
  • row_headers: the range of cells that serve as row labels (e.g., A2:A10).
  • col_lookup: the value you are searching for in the column headers (e.g., a date).
  • col_headers: the range of cells that serve as column labels (e.g., B1:D1).
  • match_type: 1 for approximate (default), 0 for exact, -1 for approximate with the smallest value greater than or equal to the lookup value.

For approximate matching, both the row and column header arrays must be sorted in ascending order. If they are not sorted, the results will be unpredictable.

Worked Example

Using the default values from the calculator: table values are “1,2,3;4,5,6;7,8,9” (a 3×3 matrix), row lookup value is 2, column lookup value is 5, and match type is 1 (approximate).

  1. Split the table string into a matrix: rows are separated by semicolons, values by commas. So the matrix is:
    Row1: 1, 2, 3
    Row2: 4, 5, 6
    Row3: 7, 8, 9
  2. Row headers are assumed to be the first column of the matrix: 1, 4, 7. Column headers are assumed to be the first row: 1, 2, 3.
  3. For the row lookup value 2 with approximate match, MATCH(2, {1,4,7}, 1) returns 1 because 2 is greater than 1 and less than 4, so the largest value ≤2 is 1, which is at position 1.
  4. For the column lookup value 5 with approximate match, MATCH(5, {1,2,3}, 1) returns 3 because 5 is greater than 3, the largest value in the array, so it returns the last position, 3.
  5. INDEX(matrix, 1, 3) returns the value at row 1, column 3, which is 3.

The calculator returns 3, which matches the manual calculation.

When to Use Approximate Match

Approximate matching is useful when you need to find the closest match in a sorted list, such as tax brackets, discount tiers, or shipping rates. For example, if you have a table of shipping costs by weight (0-10 kg, 10-20 kg, etc.), you can use approximate match to find the cost for any weight, even if the exact weight is not listed.

In a two-way lookup, approximate match works the same way for both dimensions. This is common in financial models where you look up an interest rate based on a loan amount and a credit score, where the rate table is sorted by both dimensions.

How to Choose Between Approximate and Exact

Use approximate match (match_type = 1) when:

  • The lookup values are numeric and the headers are sorted ascending.
  • You want to find the closest value that is less than or equal to the lookup value (e.g., tax brackets).
  • You are working with ranges or intervals, not discrete values.

Use exact match (match_type = 0) when:

  • The lookup values are text, IDs, or unsorted numbers.
  • You need an exact match and an error if not found.
  • The headers are not sorted.

In Excel, the default for MATCH is approximate (1) if you omit the third argument, which can lead to surprising results if your data is not sorted. Always explicitly specify the match type.

Common Mistakes and Misconceptions

One common mistake is using approximate match on unsorted data. The MATCH function assumes the lookup array is sorted in ascending order when match_type is 1. If the data is not sorted, the result is arbitrary and often wrong.

Another mistake is confusing the order of arguments in MATCH. The syntax is MATCH(lookup_value, lookup_array, [match_type]). The lookup_array is the range you are searching in, not the range you want to return.

A frequent misconception is that approximate match rounds to the nearest value. It does not; it always finds the largest value that is less than or equal to the lookup value. If you need the nearest value, you would need to use a different approach, such as the ABS function with an array formula.

Also, when using approximate match with text, the sort order is alphabetical, not by length. Ensure your headers are sorted alphabetically for text approximate matches.

Alternatives to INDEX/MATCH

In modern Excel (365 and 2021), you can use the XLOOKUP function, which simplifies two-way lookups: =XLOOKUP(row_lookup, row_headers, XLOOKUP(col_lookup, col_headers, table)). XLOOKUP defaults to exact match and does not require sorting for approximate matches (though it still uses the same logic).

In Google Sheets, you can use the FILTER function or a combination of VLOOKUP and HLOOKUP, but INDEX/MATCH remains the most flexible and widely compatible method, especially for older versions of Excel.

For simple exact matches, you could also use SUMPRODUCT, but it is less efficient for large datasets and does not handle approximate matching as cleanly.

Frequently Asked Questions

Why does my approximate match return the wrong value?

The most likely reason is that your lookup array is not sorted in ascending order. Approximate match requires the array to be sorted; otherwise, the result is unpredictable. Check that your row and column headers are sorted from smallest to largest (or A to Z for text).

Can I use approximate match with text?

Yes, approximate match works with text, but the lookup array must be sorted alphabetically. MATCH will find the largest text value that is less than or equal to the lookup value, based on the default collation order. This is rarely useful, so most text lookups use exact match.

What is the difference between match_type 1 and -1?

Match_type 1 finds the largest value less than or equal to the lookup value, assuming the array is sorted ascending. Match_type -1 finds the smallest value greater than or equal to the lookup value, assuming the array is sorted descending. Use 1 for typical bracket lookups and -1 when your data is sorted descending.

How do I handle errors when no match is found?

With approximate match, a match is almost always found because it returns the last value if the lookup value is larger than the largest array value. If you need to ensure a valid match, use exact match (0) and wrap the formula with IFERROR to return a custom message like “Not found”.

Does this work in Google Sheets?

Yes, the INDEX and MATCH functions work identically in Google Sheets. The syntax and behavior are the same, including the approximate match logic. The calculator above simulates the formula, but you can copy the formula directly into your spreadsheet.

Why use INDEX/MATCH instead of VLOOKUP?

INDEX/MATCH is more flexible because it can look to the left of the lookup column, handle two-way lookups, and does not slow down when you insert or delete columns. It also supports approximate matching in both dimensions, which VLOOKUP cannot do directly.

Related

Formula used
in t | Table values (comma-separated rows) | - | "1,2,3;4,5,6;7,8,9" | 0
in r | Row lookup value | - | 2 | 0
in c | Column lookup value | - | 5 | 0
in m | Match type (1=approximate, 0=exact) | - | 1 | 0
out result | Lookup result | - | INDEX(SPLIT(t,";"), MATCH(r, {1,2,3}, m), MATCH(c, {1,2,3}, m)) | 2

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.