Bringing data from one table into another is one of the most common tasks in Excel: finding a price by product code, a name by employee number and so on. There are two popular functions for this — VLOOKUP and XLOOKUP. In this article, based on Microsoft's official support pages, we explain how they differ and when to choose which.
How does VLOOKUP work?
VLOOKUP finds a value in a table and returns data from another column in the same row. The formula structure is:
=VLOOKUP(lookup_value; table; column_number; [FALSE])
The key rule Microsoft highlights: for VLOOKUP to work correctly, the lookup value should always be in the first column of the range (Microsoft Support — VLOOKUP). So if the code is in column A, you must select the table starting from column A.
=VLOOKUP(A2; Products!A:C; 3; FALSE).What is XLOOKUP?
XLOOKUP is designed to find things in a table or range by row. If no additional parameter is given, it looks for an exact match (Microsoft Support — XLOOKUP).
The basic structure:
=XLOOKUP(lookup_value; lookup_column; return_column)
Here the lookup column and the return column are specified separately, so there is no need to count column numbers.
Key differences in a table
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Position of lookup value | Must be in the first column of the range | Lookup column is specified separately |
| Return column | By column number | Directly as a range |
| Default match | FALSE needed for exact match | Exact match by default |
| Excel versions | All widely used versions | Microsoft 365, Excel 2021 and Excel 2024 |
Microsoft itself, on the VLOOKUP page, recommends trying XLOOKUP as an improved version that works in any direction (Microsoft Support — VLOOKUP).
Which Excel versions have XLOOKUP?
This is a very important practical question. XLOOKUP is not available in Excel 2016 and Excel 2019. It is available in Microsoft 365, Excel 2021 and Excel 2024 (Microsoft Support — XLOOKUP).
This means a colleague who opens your XLOOKUP file in an older version may not see the formula result correctly. Consider in advance who you will share the file with.
Which one to choose?
- If you use Microsoft 365 or Excel 2021/2024 — XLOOKUP is more convenient: exact match is the default and there is no need to count column numbers.
- If the file will be shared with Excel 2016 or 2019 users — VLOOKUP is the safer choice.
- Knowing both functions is best: you can work comfortably with VLOOKUP in older files and with XLOOKUP in new ones.
Summary
- In VLOOKUP the lookup value must be in the first column of the range.
- XLOOKUP searches by row and finds an exact match by default.
- XLOOKUP is not available in Excel 2016 and 2019; it is available in Microsoft 365, Excel 2021 and 2024.
- Microsoft recommends XLOOKUP as a version that works in any direction.
If you want to learn Excel functions with practical examples, message Estavor Academy on WhatsApp about the online office software course: +994 77 754 10 12.
📚 Sources
🌐 The original article is in Azerbaijani; the official sources are available in Azerbaijani.
More articles
How to create a PivotTable in Excel: step-by-step guide
How do you create a PivotTable in Excel? Preparing data, Rows and Values areas, Sum/Count, showing percentages and Refresh — based on Microsoft's guide.
How do you sign up for an online course?
How do you sign up for an online course? Estavor Akademiya's courses, who they are for, and 4 steps to start live online lessons.
What is IFRS, who applies it and what is 1C for? A guide for learners
What IFRS is, who must apply it in Azerbaijan, which rules small businesses can choose and what 1C is used for — explained for accounting learners.