VLOOKUP and XLOOKUP Examples: Formulas and Tips in Excel

For an exact match, use VLOOKUP when the value you are searching for is in the first column of the lookup range; use XLOOKUP to specify the lookup and result ranges separately. With lookup IDs in E2, IDs in A2:A20, and names in B2:B20, the formulas are =VLOOKUP(E2,A2:B20,2,FALSE) and =XLOOKUP(E2,A2:A20,B2:B20,"Not found"). Both return the matching name, while XLOOKUP can also look left and lets you supply a not-found result.

How lookup formulas work

A lookup formula finds a value in one range and returns a related value from another. For example, a sales sheet might use a product code to retrieve a product name or price from a reference table. The lookup value can be a cell reference, such as E2, or a typed value, but a cell reference is usually easier to update and reuse.

VLOOKUP searches vertically down the leftmost column of a table range. Its arguments identify the value to find, the table, the number of the column to return, and whether to use an exact or approximate match. The column number is counted from the left edge of the selected table, not from the worksheet itself. In the opening example, the name column is the second column of A:B, so the column number is 2.

XLOOKUP separates the lookup range from the return range. This makes the formula easier to read and avoids counting a return column inside a larger table. In Microsoft Excel for Microsoft 365 on Windows, XLOOKUP is available, as it is in Excel 2021 and later supported editions. VLOOKUP works in many older Excel versions too, which can matter when sharing a workbook with people using older software.

VLOOKUP exact-match example

Suppose A2:A5 contains employee IDs and B2:B5 contains names: 104 / Ana, 105 / Ben, 106 / Chen, and 107 / Daria. With the ID to find in E2 and the lookup table in A2:B5, use =VLOOKUP(E2,A2:B5,2,FALSE). If E2 contains 106, the formula returns Chen.

The final argument, FALSE, is important for IDs, names, product codes, and other values that need to match exactly. If the value in E2 is not present in the first column, VLOOKUP returns #N/A. Exact matching does not require the ID list to be sorted. In day-to-day worksheets, including FALSE explicitly helps prevent an accidental approximate lookup.

To return a salary from column C instead, with IDs in A2:A5, names in B2:B5, salaries in C2:C5, and the lookup ID in E2, use =VLOOKUP(E2,A2:C5,3,FALSE). If the row for ID 106 has a salary of 62000, the result is 62000. Keep the lookup column at the left edge of the selected table: VLOOKUP cannot search column C and return a value from column A.

XLOOKUP exact-match examples

Using the same employee table, with IDs in A2:A5, names in B2:B5, and the ID to find in E2, use =XLOOKUP(E2,A2:A5,B2:B5,"Not found"). If E2 is 106, the result is Chen. XLOOKUP uses exact matching by default, so the formula does not need a separate exact-match setting. The optional fourth argument displays Not found instead of #N/A when there is no match.

XLOOKUP can return a value to the left of the lookup column. Suppose names are in A2:A5 and IDs are in B2:B5, with the requested ID in E2. Use =XLOOKUP(E2,B2:B5,A2:A5,"Not found"). For E2 equal to 106, the result is Chen. No rearranging or helper column is needed; this is one of the clearest practical differences from VLOOKUP.

The lookup and return ranges should have compatible dimensions: each lookup row needs a corresponding return row. XLOOKUP can also return more than one adjacent column. With IDs in A2:A5, names and departments in B2:C5, and the ID in E2, use =XLOOKUP(E2,A2:A5,B2:C5,"Not found"). A match can spill the name and department into the cells to the right, provided those cells are empty. This dynamic-array behavior is available in Microsoft 365 and Excel 2021 or later.

Approximate matches for bands and thresholds

Approximate matching is useful when a value belongs to a band, such as a score mapped to a letter grade or a quantity mapped to a discount tier. It is not the right setting for most ID lookups. For VLOOKUP approximate matching, the first column of the table must be sorted in ascending order. When the fourth argument is omitted, VLOOKUP assumes approximate matching, which is a frequent source of surprising results.

For example, put minimum scores 0, 60, 70, 80, and 90 in G2:G6 and grades F, D, C, B, and A in H2:H6. If the score is in J2 and equals 84, with the thresholds sorted as shown, use =VLOOKUP(J2,G2:H6,2,TRUE). The result is B because 80 is the largest threshold that does not exceed 84. If the thresholds are out of order, the result may be wrong.

For the same sorted threshold table, with thresholds in G2:G6, grades in H2:H6, and the score in J2, use =XLOOKUP(J2,G2:G6,H2:H6,"",-1). The match-mode argument -1 means exact match or the next smaller value. For a score of 84, this returns B. XLOOKUP also has other match modes, but for tier tables the next-smaller option is often the relevant one. Check that the thresholds and the meaning of each band match your intended rules.

Choosing between VLOOKUP and XLOOKUP

For a new workbook used in a current version of Excel, XLOOKUP is generally more flexible. VLOOKUP remains useful when a workbook must work in older Excel versions or when a team already uses established VLOOKUP formulas. The best choice depends on compatibility, the shape of the data, and how the workbook will be maintained.

Choose XLOOKUP when you need to look in either direction, want an explicit not-found message, or want to avoid a numbered return-column argument.
Choose VLOOKUP when compatibility with older Excel installations is important and your lookup value is in the leftmost column of the table.
Use exact match for identifiers such as account numbers, employee IDs, and product codes. Approximate matching is intended for ordered bands, not for loosely similar text.
Consider maintenance when columns might be inserted or moved. XLOOKUP points directly to the return range; a VLOOKUP column number may need updating if the table structure changes.

Neither function automatically solves every data-quality problem. If two rows share the same lookup value, both functions normally return the first matching row they encounter. A lookup formula does not know which duplicate is correct; the source table or the lookup rule needs to resolve that ambiguity.

Common errors and troubleshooting

#N/A usually means an exact match was not found. Check for extra spaces, different spellings, or numbers stored as text in one range but as numeric values in the other. A code that looks like 0017 may also be stored differently from the number 17; leading zeros can be significant in identifiers. Normalize the source data when possible rather than masking a mismatch with a more permissive lookup.

With VLOOKUP, #REF! can indicate that the requested column number is larger than the number of columns in the selected table. If you select A2:B20 but ask for column 3, there is no third column in that range. VLOOKUP can also return the wrong field without an error if the column number still points to a valid but unintended column after the table changes.

With XLOOKUP, verify that the lookup and return ranges start and end on corresponding rows. Also check that the spill area is clear if the formula should return multiple columns; occupied cells can produce a spill error. If an approximate VLOOKUP gives an unexpected result, confirm the first lookup column is sorted ascending and that the final argument is set intentionally.

Compare the actual cell contents, not just their displayed formatting.
Check for leading or trailing spaces and inconsistent punctuation.
Confirm the lookup range includes every relevant row and the intended column.
Use exact matching for unique keys; use approximate matching only for deliberately ordered thresholds.
Test the formula with a value you can verify in the source table.

Compatibility and practical workbook tips

The examples use comma separators, as in the standard English-language Excel syntax. Depending on Windows regional settings, Excel may require semicolons between arguments instead. Excel for Mac and Excel for the web offer the same basic lookup functions when the relevant function is supported by the user’s Excel version, though menus and interface details can differ. These examples do not depend on a special ribbon command; enter a formula in a worksheet cell and press Enter.

XLOOKUP is not available in some older Excel releases, so a workbook containing it may show a function-name error when opened there. If recipients use older versions, test compatibility or use VLOOKUP or another supported approach. A formula’s availability can matter even when it calculates correctly on the computer where it was created.

For readability, keep lookup and return ranges focused on the data rather than selecting entire columns without a reason. Use clear headers, keep the key values consistent, and make sure the return column actually corresponds to the key. If the source list grows regularly, an Excel Table can make references easier to manage; structured references also make a formula’s purpose clearer to collaborators. Whatever the method, document unusual matching rules, especially approximate thresholds.

Frequently Asked Questions

Is XLOOKUP better than VLOOKUP?
XLOOKUP is more flexible for many new workbooks because it can look left or right, defaults to exact matching, and allows a not-found result. VLOOKUP can be the better practical choice when older Excel compatibility is required.

Does VLOOKUP need the lookup column to be first?
Yes. VLOOKUP searches the first, leftmost column of the selected table range and returns a value from a column to its right. If the return value is to the left, use XLOOKUP in a supported Excel version or restructure the data.

Why does VLOOKUP return the wrong result?
A common cause is leaving the final argument out, which makes VLOOKUP use approximate matching. For an exact ID or text match, set that argument to FALSE. For approximate matching, sort the first column in ascending order and verify the band thresholds.

What should I use when a lookup value is missing?
VLOOKUP normally returns #N/A when it cannot find an exact match. XLOOKUP lets you provide a message such as Not found as its fourth argument. Before displaying a custom message, check whether the value is truly absent or merely formatted differently.

Can XLOOKUP return multiple columns?
Yes. In Microsoft 365 and Excel 2021 or later, its return array can include multiple columns, and results can spill into neighboring cells. The destination cells must be empty, and the selected return range must align with the lookup range.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.