Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 93 ยท Lookup and Reference

INDEX and MATCH

Learn how to combine INDEX and MATCH in Excel for left lookups, two-way lookups and lookups that survive inserted columns, with live examples.

VLOOKUP does two jobs in one function: it finds a row and it returns a column. INDEX and MATCH split those jobs. MATCH answers "at which position is this key?", and INDEX answers "what is the value at this position?". Put them together and you get a lookup that can look left, does not depend on a hard-coded column number, and can search in two directions at once.

Excel cannot run on this site, so the formulas below are real Excel formulas and the live examples run them on a small JavaScript stand-in engine: it parses formulas, follows cell references and implements INDEX and MATCH with the same rules. The engine is a teaching model, not Excel's actual code.

Two small jobs

MATCH Where is the key? Returns a position.
INDEX What is at that position? Returns a value.

INDEX(values, MATCH(key, keys, 0))

1 / 2

MATCH: find the position

TEXT
=MATCH(lookup_value, lookup_array, [match_type])

lookup_array is a single row or a single column. The result is the position inside that range, starting at 1, not a sheet row number. If the names are in B2:B5 and Lena is the third name, =MATCH("Lena", B2:B5, 0) returns 3.

The third argument decides how matching works:

match_type Meaning Requirement
0 exact match (text may use * and ? wildcards) none
1 (default) largest value less than or equal to the key sorted ascending
-1 smallest value greater than or equal to the key sorted descending

As with VLOOKUP, the default is not exact. Type the 0 as a reflex. Types 1 and -1 are the approximate modes from HLOOKUP and Approximate-Match Lookups, and carry the same warning about sorting.

INDEX: get the value at a position

TEXT
=INDEX(array, row_num, [column_num])

INDEX returns the cell at a given row and column inside the range you give it. For a one-column range you only need the row number:

TEXT
=INDEX(D2:D5, 3)          the third salary
=INDEX(A2:D5, 3, 2)       row 3, column 2 of the block

For a single-row range, one number is a column position. A row or column number of 0 returns the whole row or column (useful inside SUM or in dynamic arrays). A position outside the range gives #REF!.

Combine them

Replace the number in INDEX with a MATCH:

TEXT
=INDEX(D2:D5, MATCH("Lena", B2:B5, 0))

Read it from the inside: MATCH finds Lena in the name column and returns 3, INDEX returns the 3rd value of the salary column. The key column (B2:B5) and the result column (D2:D5) are separate arguments, so nothing forces one to be left of the other.

The first live example runs a left lookup, a two-way lookup in a sales grid, the three match types, and two failures. Read the code first, then the output.

JS INDEX and MATCH: left lookup, two-way lookup, match types

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Look at what each line proves:

  • A left lookup works: the ID E-47 sits in column A, left of the names that were searched, and INDEX(A2:A5, MATCH("Ravi", B2:B5, 0)) returns it. VLOOKUP cannot do this directly.
  • INDEX(A2:D5, MATCH(...), 3) takes the column number from you. Use it when you want to choose the column by position.
  • The two-way lookup uses two MATCH calls: one finds the region's row, one finds the month's column, and INDEX returns the cell where they cross (East ร— Mar is 130).
  • Match type 1 on a sorted band list returned position 4, the 80 band for 85. Type 0 returned #N/A, because no value equals 85. Match type -1 on a descending list returned the position of 75, the smallest price that is still at least 60.
  • A missing name gives #N/A, wrapped here in IFNA. (See Handling Errors and Blanks in Formulas for why IFNA is better than IFERROR here.)

Two-way lookups

A grid with labels down the side and across the top is common: regions by month, products by size, employees by weekday. Looking up one cell by both labels is the job of the pattern:

TEXT
=INDEX(data, MATCH(row_label, row_labels, 0), MATCH(column_label, column_labels, 0))

where data is just the numbers (B9:E11 above), row_labels is the column of regions (A9:A11) and column_labels is the row of months (B8:E8). Because the labels are looked up, you can drive both from dropdown cells (see Data Validation and Dropdown Lists) and build a small report that changes when a user picks a region and a month. VLOOKUP needs a hard-coded column number for this; INDEX/MATCH does not.

Why INDEX and MATCH survive column changes

Take =INDEX(D2:D5, MATCH("Lena", B2:B5, 0)). Insert a new column before column D. Excel updates every reference that points at moved cells, so the formula silently becomes =INDEX(E2:E5, MATCH("Lena", B2:B5, 0)) and still returns the salary. A VLOOKUP with a hard-coded 4 would now point at the wrong column (see the example in VLOOKUP and Its Limits). When references are made of ranges instead of counted positions, Excel can protect you.

Performance: one MATCH, many INDEX calls

If a row needs several fields, you can store the match position in a helper cell once and use it in many INDEX formulas. INDEX reads by position and does no searching. The stand-in below counts comparisons on a 100,000-row table (it counts operations, not seconds):

JS Counting comparisons: three VLOOKUPs vs one MATCH

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Three separate searches cost three times as many comparisons as one search plus three position reads. Modern Excel versions also cache work for repeated lookups on the same range, so the real-world gap is smaller than this simple count suggests, but a helper MATCH is still a clean pattern: the formula is shorter and the key is searched once. For a very large table that you must search often, sort it and use an approximate or binary-search lookup (XLOOKUP offers this with a search mode, see XLOOKUP and XMATCH).

Mistakes to avoid

  • Misaligned ranges. The INDEX range and the MATCH range must cover the same rows (D2:D5 and B2:B5). If one starts a row higher, you get a neighbor's value, with no error.
  • Forgetting 0. Leaving out the third argument of MATCH means "approximate, sorted ascending".
  • Using the position as a row number. MATCH returns 3 for the third item of the range, even if that item is on sheet row 4. Combine it with INDEX, or add the starting row yourself.
  • Hiding errors. Wrap in IFNA only when "not found" is a normal outcome.

For readability, give the ranges names (see Ranges and Named Ranges): =INDEX(Salary, MATCH("Lena", Name, 0)) reads almost like English.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does =MATCH("Lena", B2:B5, 0) return if Lena is the third name in B2:B5?

    MATCH returns the position inside the range, counting from 1. It is not a sheet row number and it is not the value itself.
  2. 2. Which task is the main advantage of INDEX/MATCH over VLOOKUP?

    The key range and the result range are separate arguments, so the result can be in any column, including one left of the key column.
  3. 3. Which match_type value gives an exact match in MATCH?

    0 means exact match. 1 (the default) is approximate on ascending data and -1 is approximate on descending data.
  4. 4. How do you build a two-way lookup in a grid with labels down the side and across the top?

    One MATCH finds the row position, a second MATCH finds the column position, and INDEX returns the cell at their crossing.
  5. 5. INDEX(D2:D5, 9) is evaluated on a four-row range. What is the result?

    A position outside the range is an invalid reference, so INDEX returns #REF!. #N/A is what MATCH returns when it finds nothing.

Summary

  • MATCH(key, range, 0) returns the position of an exact match; types 1 and -1 do approximate matching on sorted data.
  • INDEX(range, row, [column]) returns the value at a position; 0 returns a whole row or column.
  • INDEX(result_range, MATCH(key, key_range, 0)) looks up in any direction and is not tied to a hard-coded column number.
  • A two-way lookup uses one MATCH for the row and one for the column.
  • Keep the INDEX and MATCH ranges aligned, type the 0, and use IFNA only for expected misses.

Next, the modern replacement that does the same job in a single function: XLOOKUP and XMATCH.