XLOOKUP vs VLOOKUP: what changed and which to use
XLOOKUP vs VLOOKUP side by side: exact match by default, looking left, no column numbers, built-in not-found text. With formulas, and when VLOOKUP still fits.
Find the name that goes with each email.
XLOOKUP needs three things: what to find, where to find it, and what to bring back.
Find the name for each email in E2:E6. Put the formulas in F2:F6.
Typing formulas is easiest on a computer with a keyboard.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Team | Name | ||||
| 2 | Tariq Li | tariq.li@example.com | West | kofi.patel@example.com | |||
| 3 | Nadia Patel | nadia.patel@example.com | West | sam.okafor@example.com | |||
| 4 | Daniel Fischer | daniel.fischer@example.com | North | jamal.novak@example.com | |||
| 5 | Quinn Evans | quinn.evans@example.com | West | rosa.jensen@example.com | |||
| 6 | Jamal Novak | jamal.novak@example.com | North | tariq.li@example.com | |||
| 7 | Isla Kowalski | isla.kowalski@example.com | South | ||||
| 8 | Kofi Patel | kofi.patel@example.com | South | ||||
| 9 | Yusuf Dubois | yusuf.dubois@example.com | North | ||||
| 10 | Sam Okafor | sam.okafor@example.com | North | ||||
| 11 | Rosa Jensen | rosa.jensen@example.com | East | ||||
| 12 | |||||||
| 13 | |||||||
| 14 | |||||||
| 15 |
XLOOKUP?XLOOKUP is the modern replacement for VLOOKUP. Instead of a table and a column number, you give it two columns: where to search and what to return.
=XLOOKUP(lookup_value,
lookup_array: the column to search, e.g. B2:B11.return_array: the column with the answers, e.g. A2:A11.VLOOKUP?Because the two columns are separate, the answer can be to the left of the search column, something VLOOKUP can't do. XLOOKUP also matches exactly by default. XLOOKUP vs VLOOKUP compares the two side by side.
XLOOKUP vs VLOOKUP side by side: exact match by default, looking left, no column numbers, built-in not-found text. With formulas, and when VLOOKUP still fits.
Join two Excel files on a shared column like an ID or email: with XLOOKUP, Power Query, or in your browser with no formulas. Handles missing and duplicate keys.
Compare two Excel sheets cell by cell or row by row. Formulas to find missing rows and changed values, conditional formatting, and a no-formula option.
Know someone fighting with a spreadsheet? Send them this page.