How to use VLOOKUP and XLOOKUP in Excel

A free, step-by-step guide for Excel on Windows · Last checked: 26 Oct 2026

Both functions look for a value in one column and give you the matching value from another. XLOOKUP is Microsoft's newer, improved version. VLOOKUP works in older Excel too. Microsoft's page for XLOOKUP says it is not available in Excel 2016 and Excel 2019.

XLOOKUP

Formula (from Microsoft): =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). Only the first three parts are required.

Click the cell where you want the answer.
Type =XLOOKUP(.
lookup_value: the value to find, or the cell that holds it (for example F2).
lookup_array: the column to search in (for example B2:B11).
return_array: the column to give the answer from (for example D2:D11).
Close the bracket and press Enter: =XLOOKUP(F2,B2:B11,D2:D11).

By default XLOOKUP looks for an exact match. If nothing is found it returns #N/A; to show your own text instead, add the fourth part, for example =XLOOKUP(F2,B2:B11,D2:D11,"Not found").

VLOOKUP

Formula (from Microsoft): =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).

Click the cell for the answer and type =VLOOKUP(.
lookup_value: the value to find.
table_array: the whole table, from the column that holds the lookup value to the column with the answer, for example A2:C7.
col_index_num: which column of that table holds the answer, counting the first column as 1.
range_lookup: type FALSE for an exact match. Microsoft's example is =VLOOKUP(102,A2:C7,2,FALSE): it looks for 102 in column A and returns the matching value from column B.

If something goes wrong

#N/A with VLOOKUP. With FALSE it means the exact value was not found. Check for extra spaces or numbers stored as text. If you leave out the last part, Microsoft says VLOOKUP assumes the first column is sorted (approximate match), which gives wrong answers on unsorted data. Use FALSE for exact matches.
The answer column is to the left. Microsoft says VLOOKUP's lookup value must be in the first column of the table, so the answer must be to its right. XLOOKUP can search in any direction.

Stay safe

Check a few results by hand before you rely on them, especially for money, marks or payroll.
Keep a copy of your workbook before you fill formulas down a whole column.

Pocko is not part of Microsoft and this guide is not official. Menus and functions differ a little between Excel versions; if a step looks different, check Microsoft's own help.

Sources

Last checked: 26 Oct 2026

Stuck on a step? Pocko sits on your screen and points at the next button. Join the waitlist.