Looking up a value in a table is one of the most common jobs in Excel: find a product price by its code, an employee name by an ID, or a grade by a student number. For years the answer was VLOOKUP. Today there is also XLOOKUP, a newer function designed to fix VLOOKUP’s biggest frustrations. Here is how each one works and how to choose.
What VLOOKUP does
VLOOKUP searches for a value in the first column of a range and returns a value from another column in the same row. The V stands for vertical. Its syntax looks like this:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])- lookup_value: what you are looking for, such as a product code.
- table_array: the range that contains the data.
- col_index_num: the number of the column to return, counting from the first column of the range.
- range_lookup: FALSE (or 0) for an exact match, TRUE (or 1) for an approximate match.
For example, =VLOOKUP(A2, F2:H100, 3, FALSE) looks for the value in A2 in column F and returns the value from the third column of the range, which is column H.
Where VLOOKUP gets in the way
VLOOKUP is simple, but it has well-known limitations that cause errors in real spreadsheets:
- It only looks to the right. The lookup value must be in the first column, and the result must be to its right. If the ID is in column D and the name is in column B, VLOOKUP cannot do it directly.
- The column number is hard-coded. If someone inserts or deletes a column inside the range, the number 3 may now point to the wrong column, and nothing warns you.
- The default is approximate match. If you forget the last argument, VLOOKUP assumes TRUE, which expects sorted data and can return wrong answers without any error.
- It returns only one column per formula.
What XLOOKUP does
XLOOKUP is available in Microsoft 365 and in recent versions of Excel (2021 and later). It replaces the column number with two separate ranges: one to search and one to return. The basic syntax is:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])The same example becomes =XLOOKUP(A2, F2:F100, H2:H100). You select exactly the column to search and exactly the column to return, so there is no counting and inserting a column in the middle no longer breaks the formula. By default, XLOOKUP performs an exact match, which is what people want most of the time.
Advantages of XLOOKUP
- Looks in any direction. The return range can be to the left or right of the search range.
- Built-in error handling. The fourth argument lets you display a message such as "Not found" instead of #N/A, with no need for IFERROR.
- Exact match by default, which reduces silent mistakes.
- Flexible matching. You can choose exact, next smaller, next larger, or wildcard matching.
- Search direction. It can search from the last item to the first, useful to find the most recent entry for a customer.
- Can return several columns. If the return array has multiple columns, the result spills into neighboring cells in versions that support dynamic arrays.
Side-by-side comparison
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Search direction | Left to right only | Any direction |
| Return column | Column number inside the range | Separate return range |
| Default match | Approximate | Exact |
| Handles missing values | Needs IFERROR | Built-in argument |
| Search from the bottom | No | Yes |
| Availability | All versions | Microsoft 365 and Excel 2021+ |
A practical example
Imagine a sheet with order IDs in column A and customer names in column D. You want to find the customer for the order typed in cell G1. With VLOOKUP, you would first have to rearrange the columns or build a helper formula. With XLOOKUP, you write:
=XLOOKUP(G1, A:A, D:D, "Order not found")The formula is shorter, easier to read, and it shows a friendly message if the ID does not exist. Anyone opening the file later can understand it at a glance.
When VLOOKUP is still fine
VLOOKUP is not obsolete. It works in every version of Excel and in many other spreadsheet programs, and it is everywhere in existing workbooks. If you share files with people who use older versions, XLOOKUP will show an error on their computers. In that situation, VLOOKUP, or the INDEX and MATCH combination, is the safer option. You should also be able to read VLOOKUP formulas, because you will inevitably inherit spreadsheets that use them.
Tips to avoid common mistakes
- Always set an exact match when you need one: use FALSE in VLOOKUP, or rely on XLOOKUP’s default.
- Check for extra spaces and mismatched data types. A number stored as text will not match a real number.
- Use absolute references (such as $F$2:$H$100) when copying the formula down.
- Convert your data to an Excel Table so ranges grow automatically as you add rows.
- Test with a value you know exists and one that does not.
Conclusion
If your version of Excel supports it, XLOOKUP is generally the better tool: it is more flexible, safer, and easier to read. VLOOKUP remains useful for compatibility and for understanding older files. Knowing both makes you more productive and more confident with data. To build these skills step by step, explore the Excel and office productivity courses available on Cursa.
