Lookup formulas help you find a value in a table and return related information, such as finding a course code and returning its course name. XLOOKUP offers a more flexible syntax in supported Excel versions, while VLOOKUP remains common in older workbooks and compatibility workflows. Choose based on the Excel version your team actually uses.
A simple lookup example
Suppose A2:A100 contains product IDs and B2:B100 contains product names. Cell E2 contains the ID you want to find. You need Excel to locate that ID and return its matching name.
Before writing a formula, check that identifiers are consistent: a text ID such as 00125 may not match a numeric 125, and duplicate IDs can produce unexpected matches.
- Keep the lookup key in one clearly identified column.
- Check whether IDs are unique.
- Remove accidental leading or trailing spaces where appropriate.
How VLOOKUP works
VLOOKUP searches for a value in the first column of a selected table range and returns a value from a column to the right. The column index is counted from the left edge of the selected range, so inserting columns inside that range can make a hard-coded index fragile.
For an exact match, use FALSE as the fourth argument. Leaving the match argument out can allow approximate matching, which is not appropriate for many ID lookups.
=VLOOKUP(E2,A2:B100,2,FALSE)How XLOOKUP works
XLOOKUP separates the lookup range from the return range. You do not need to count a column index, and you can return a value from a column to the left or right of the lookup range. It also supports a result to show when no match is found.
XLOOKUP is not available in every older Excel version, so check compatibility before sharing a workbook with people who use legacy versions.
=XLOOKUP(E2,A2:A100,B2:B100,"Not found")Important differences at a glance
For a simple exact-match lookup, both formulas can work. XLOOKUP is generally easier to read and maintain because it uses explicit lookup and return ranges. VLOOKUP may be necessary when the workbook must run in an older Excel version that does not support XLOOKUP.
- Lookup direction: VLOOKUP returns from columns to the right; XLOOKUP can return from either side.
- Column changes: VLOOKUP uses a numeric column index; XLOOKUP uses a separate return range.
- Not found: XLOOKUP has a built-in optional not-found result.
- Compatibility: older Excel editions may not support XLOOKUP.
- Match mode: specify the intended match behavior, especially for unsorted data.
Debugging a lookup that returns the wrong result
If a formula returns #N/A or an unexpected match, check the source key, cell types, spaces and duplicate values. In VLOOKUP, confirm that the lookup column is the first column in the selected range and the match argument is FALSE for exact-match work.
Do not hide every error as a blank until you know why it occurred. A missing ID can be a meaningful data-quality problem that should be fixed rather than concealed.
=XLOOKUP(E2,A2:A100,B2:B100,"Check ID")Understanding the concept is the first step.
Explore related resources or learn about subject-focused tutoring designed to help you understand the material.