Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 29 ยท Lookups and Dates

XLOOKUP, INDEX and MATCH

Replace VLOOKUP with XLOOKUP, or INDEX and MATCH, in Google Sheets: look left, return many columns, do two-way and multi-criteria lookups.

VLOOKUP gets the job done, but it only looks right, it counts columns by number, and it stops at the first match. Google Sheets has two better approaches: XLOOKUP, a modern one-function lookup, and the INDEX + MATCH pair, which works in every spreadsheet program and is still the most flexible building block.

In this lesson you will learn both, then use them for lookups that VLOOKUP cannot do: searching to the left, searching from the bottom, returning several columns, finding a value in a grid by row and column names, and matching on more than one condition.

Two modern lookup tools

XLOOKUP Key range, result range, optional fallback.
INDEX Returns the cell at a given row and column.
MATCH Returns the position of a key.

INDEX plus MATCH equals one flexible lookup.

1 / 2

XLOOKUP

The syntax is XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]). You point at two separate ranges: where to look, and what to return. They must be the same height (or width).

TEXT
=XLOOKUP(F2, B2:B100, A2:A100, "Not found")

This looks for F2 in the codes in column B and returns the matching name from column A, which is to the left, impossible for VLOOKUP. The fourth argument is shown when there is no match, so you do not need IFNA.

Useful options:

  • match_mode: 0 exact (default), -1 exact or the next smaller value, 1 exact or the next larger, 2 wildcard match (* and ?).
  • search_mode: 1 first to last (default), -1 last to first. Searching from the bottom returns the most recent entry when a key appears several times, such as the latest price for a product.
  • Several columns: if result_range is C2:E100, the formula returns all three values of the matching row, spilling across cells (leave them empty).

The approximate-match banded lookups from the previous lesson become =XLOOKUP(F2, A2:A6, B2:B6, "n/a", -1), and unlike VLOOKUP, -1 means "next smaller" explicitly instead of hiding behind a default.

Google Sheets itself cannot run on this page, so formulas are read-only. The TryIt blocks are Python stand-ins that reproduce the logic, so you can see what each function does.

Python Stand-in: XLOOKUP with fallback, direction and match modes

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

INDEX and MATCH

XLOOKUP is newer; INDEX and MATCH are older and appear in countless existing sheets, so learn both.

MATCH(key, range, [type]) returns the position of the key within a one-dimensional range. Use type 0 for an exact match. If the key is the third item, the answer is 3. The default type 1 is approximate and needs sorted data, the same trap as VLOOKUP, so type the 0.

INDEX(range, row, [column]) returns the cell at a position inside a range. INDEX(C2:C100, 3) is the third cell of that column.

Combine them. MATCH finds the position, INDEX fetches the value there:

TEXT
=INDEX(A2:A100, MATCH(F2, B2:B100, 0))

This does exactly what the XLOOKUP example did: find F2 in column B and return the name from column A. The result range can be any column, because MATCH and INDEX are separate steps. There is no hard-coded column number, so inserting a column in the middle does not break the formula.

Two-way lookups

A grid has labels along both the top and the left side: products down, months across. Finding "Pen in March" needs two positions, one for the row and one for the column:

TEXT
=INDEX(B2:E5, MATCH("Pen", A2:A5, 0), MATCH("Mar", B1:E1, 0))

The first MATCH finds the row number among the product names, the second finds the column number among the month headers, and INDEX returns the cell where they cross. Nothing is hard-coded, so adding a month column keeps it working as long as the ranges grow with it. This pattern is the foundation of dashboards where a dropdown (see Data Validation and Dropdowns) selects the row and column.

Python Stand-in: INDEX and MATCH, and a two-way lookup

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Multiple criteria

What if one column is not enough to identify a row? For instance, the same product has different prices in different regions. You need to match product AND region.

A direct trick uses boolean arithmetic: comparing a range with a value gives a column of TRUE/FALSE, and multiplying two such columns gives 1 only where both are true.

TEXT
=ARRAYFORMULA(XLOOKUP(1, (A2:A100=F2) * (B2:B100=G2), C2:C100, "No match"))

Reading it: row by row, (A=F2)*(B=G2) is 1 when product and region both match, 0 otherwise. XLOOKUP looks for the first 1 and returns the price from column C. The ARRAYFORMULA wrapper makes Sheets evaluate the comparisons over whole ranges; see Array Formulas and ARRAYFORMULA.

A second approach builds a combined key with a helper column, =A2&"|"&B2, and searches for F2&"|"&G2. It is easy to read and quick to debug, at the cost of an extra column. For summing instead of looking up, SUMIFS handles multiple criteria directly (SUMIF, COUNTIF, AVERAGEIF and Friends).

Python Stand-in: multiple criteria with multiplied conditions

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Which should you use?

Need Choose
Quick, readable lookup in Google Sheets XLOOKUP
Workbook that will also open in older Excel or other tools INDEX + MATCH
Two-way lookup in a grid INDEX + two MATCH
Latest matching entry XLOOKUP with search mode -1
Return a whole row or several columns XLOOKUP with a multi-column result range

Whichever you choose, keep the same habits: trim your keys, keep numbers as numbers, use absolute references for ranges you copy down (Relative, Absolute and Mixed References), and show a clear message instead of #N/A when a miss is normal. For dynamic filtering of many matches rather than one, see FILTER in Sorting and Filtering Data.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is a main advantage of XLOOKUP over VLOOKUP?

    XLOOKUP takes separate lookup and result ranges, so the result can be in any position relative to the key.
  2. 2. What does =MATCH("Pen", A2:A10, 0) return if Pen is the third item in the range?

    MATCH returns the position within the range (starting at 1), not the value or the cell address.
  3. 3. In =INDEX(B2:E5, MATCH("Pen", A2:A5, 0), MATCH("Mar", B1:E1, 0)), what does the second MATCH provide?

    The first MATCH gives the row position among the products, the second gives the column position among the month headers.
  4. 4. Which XLOOKUP setting returns the most recent entry when a key appears several times in chronological order?

    Searching from the bottom up finds the last matching row first.

Summary

  • XLOOKUP(key, lookup_range, result_range, missing, match_mode, search_mode) looks in any direction and has a built-in not-found value.
  • MATCH(key, range, 0) returns a position; INDEX(range, row, col) returns the cell at a position; together they replace VLOOKUP with no hard-coded column number.
  • A two-way lookup uses INDEX with two MATCH calls, one for the row and one for the column.
  • Multiple criteria work by multiplying conditions (inside ARRAYFORMULA) or by building a combined key.
  • Always use exact match (0) unless you really want a banded lookup on sorted data.

Next: Dates and Times