# VLOOKUP and XLOOKUP in Google Sheets (Google Sheets lesson 3 of 17)

> Pull a name, a price or a rate from another table by its code.

Source: https://spreadsheetpractice.com/google-sheets/learn/vlookup-xlookup · Updated October 29, 2026

- **Course:** [Google Sheets course](https://spreadsheetpractice.com/google-sheets), track "Sheets essentials"
- **Time:** about 10 minutes, 3 graded exercises
- **Functions:** `VLOOKUP`, `XLOOKUP`, `IFNA`, `INDEX`, `MATCH`
- **Access:** free
- **Key formula:** `=VLOOKUP(A2,$F$2:$H$7,2,FALSE)`: Exact match, second column of the table

## How it works

`VLOOKUP` finds a value in the first column of a range and returns the cell a set number of columns to the right: `=VLOOKUP(A2, $F$2:$H$7, 3, FALSE)`. In Google Sheets the last argument is called `is_sorted`, and it defaults to TRUE, which means an approximate match on a sorted list. For codes, names and IDs, always pass FALSE.

`XLOOKUP` is available in Google Sheets too. You point at the column to search and the column to return, so there is no column number to count, and the return column can sit to the left of the search column: `=XLOOKUP(K5, $G$2:$G$7, $F$2:$F$7)`. A fourth argument sets what to show when nothing matches.

A missing code returns `#N/A`. Wrap a VLOOKUP in `IFNA` to show a message instead: `=IFNA(VLOOKUP(...), "Not found")`. IFNA only catches #N/A, so a broken reference or a typo still shows its own error.

`INDEX` and `MATCH` do the same job in two steps. MATCH finds the position of the value, and INDEX returns the cell at that position in another column: `=INDEX($H$2:$H$7, MATCH(K5, $G$2:$G$7, 0))`. You'll see the pair in older sheets and in files shared with Excel users.

## Syntax

| Formula | What it does |
|---|---|
| `=VLOOKUP(A2,$F$2:$H$7,2,FALSE)` | Exact match, second column of the table |
| `=XLOOKUP(A2,$F$2:$F$7,$H$2:$H$7)` | Search one column, return another |
| `=XLOOKUP(K5,$G$2:$G$7,$F$2:$F$7,"None")` | Return a column to the left, with a fallback |
| `=IFNA(VLOOKUP(K2,$F$2:$H$7,2,FALSE),"Not found")` | A message instead of #N/A |
| `=INDEX($H$2:$H$7,MATCH(K5,$G$2:$G$7,0))` | The two-step version |

## Watch out for

- Leaving off FALSE makes VLOOKUP assume the first column is sorted. On unsorted codes it can return the wrong row with no error.
- Lock the lookup table with $ before filling down, or the range slides off the table one row at a time.
- VLOOKUP's column number counts from the first column of the range, not from column A.
- IFERROR hides every error, including a broken reference. Around a lookup, IFNA is the safer choice.

## The exercises

Solve them in the live grid at [VLOOKUP and XLOOKUP in Google Sheets](https://spreadsheetpractice.com/google-sheets/learn/vlookup-xlookup). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **Item names with VLOOKUP.** In C2, use VLOOKUP to bring back the item name for the SKU in A2 from the price list in F2:H7. Lock the table, then fill down to C6.
2. **Prices with XLOOKUP.** In D2, use XLOOKUP to return the price for the SKU in A2: search the SKUs in F2:F7 and return from H2:H7. Fill down to D6.
3. **Missing codes and left lookups.** In K3, return the item name for the SKU in K2 with VLOOKUP, and show "Not found" when the SKU isn't in the list. In K6, use XLOOKUP to return the SKU for the item in K5, showing "No such item" when it's missing. In K7, return that item's price with INDEX and MATCH.

Previous: [COUNTIF, SUMIF and the IFS versions](https://spreadsheetpractice.com/google-sheets/learn/countif-sumif) · Next: [FILTER and SORT, the Sheets way](https://spreadsheetpractice.com/google-sheets/learn/filter-and-sort)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
