# Look up the right value

XLOOKUP, VLOOKUP and INDEX + MATCH.

FormulaSmith | 2026-10-08 | EN

English Excel: comma arguments. Regional settings may use another separator.

Import sales.csv at A1 (comma) or paste sales.tsv at A1. Keep the English sample values unchanged.

| ID | Region | Amount | Status |
| --- | --- | --- | --- |
| P101 | North | 120 | Paid |
| P102 | South | 80 | Open |
| P103 | North | 200 | Paid |
| P104 | West | 150 | Open |
| P105 | North | 50 | Open |
| P106 | South | 100 | Paid |

## Modern exact lookup

```excel
=XLOOKUP("P103",A2:A7,C2:C7)
```

RESULT: 200

Exact match is the default. The first match wins.

## Classic exact lookup

```excel
=VLOOKUP("P103",A2:D7,3,FALSE)
```

RESULT: 200

FALSE is essential for an exact ID match. 3 counts from column A.

## INDEX + MATCH

```excel
=INDEX(C2:C7,MATCH("P103",A2:A7,0))
```

RESULT: 200

MATCH returns a relative position; 0 means exact match.

## A missing key

```excel
=XLOOKUP("P999",A2:A7,C2:C7,"Missing")
```

RESULT: Missing

An explicit missing label is not the same as a genuine zero.

## Last matching record

```excel
=XLOOKUP("North",B2:B7,A2:A7,"Missing",0,-1)
```

RESULT: P105

-1 searches bottom to top. Last row does not mean latest date.

## Check key uniqueness

```excel
=COUNTIF(A2:A7,"P103")
```

RESULT: 1 matching record

0 means missing; above 1 means duplicate keys. A lookup can hide them.

## BEFORE YOU USE IT

XLOOKUP requires Microsoft 365, Excel 2021 or newer; not Excel 2016/2019. The VLOOKUP and INDEX + MATCH recipes are alternatives for older versions.

## MICROSOFT REFERENCES

- [XLOOKUP](https://support.microsoft.com/en-us/excel/functions/xlookup-function) (2026-10-08)
- [VLOOKUP](https://support.microsoft.com/en-us/excel/functions/vlookup-function) (2026-10-08)
- [MATCH](https://support.microsoft.com/en-us/excel/functions/match-function) (2026-10-08)

https://formulasmith.com/en/cheat-sheets/