Since releasing version 1.0 for Macintosh in 1985, Excel has included VLOOKUP. Now, after decades of helping users carry over information from one part of a spreadsheet to another, the VLOOKUP function is heading for retirement.
The V — which stands for vertical — is a reference to the function’s ability to retrieve information in vertical columns. A similar function, HLOOKUP, works with data that is in horizontal rows.
Although VLOOKUP was the third most-used function for Excel, after SUM and AVERAGE, it had some notable limitations. Results defaulted to an approximate match of what the user was looking for, instead of the exact match. Microsoft introduced a feature called XLOOKUP, which was designed to address those shortcomings.
If you have a Microsoft 365 subscription, you will be able to perform these XLOOKUP features:
- Look up vertically (vs. VLOOKUP) or horizontally (vs. HLOOKUP)
- Code can be in any column/row of the table
- Specify an entry to return if no match is found
- Search from the top down (or left to right) or the bottom up (or right to left)
- Use wildcard characters such as * and ?
- Return a reference to a cell
Overall, XLOOKUP is a major improvement over VLOOKUP. It provides more flexibility and efficiency for those using Microsoft 365.