VLOOKUP in Excel still works, and most of the VLOOKUPs already running in your business should be left exactly where they are. The real decision is not whether the formula is old. It is whether everyone who opens the file could use the replacement, and whether the lookup is the thing that is actually broken.
Table of Contents
Should you still be using VLOOKUP?
Yes, in most existing workbooks. VLOOKUP is not deprecated and it still works in current Excel. A formula that has returned the right answer for three years is not a defect. The case for switching applies to what you write next, not to what already works. Rewriting a working file carries risk nobody asked for.
That position runs against most advice you will find, which treats XLOOKUP as an upgrade you are overdue for. The reason to push back is that when a workbook stops being trusted, the lookups are rarely where the problem started. Structure breaks. Source data breaks. People break things by pasting. The formula sitting on top of all that is doing what it was told.
The Australian Bureau of Statistics, in Characteristics of Australian Business 2024-25 released in June 2026, reports that 10% of businesses actively collected or analysed data to make informed decisions, down from 24% in 2021-22. The ABS redeveloped that survey for 2024-25 and cautions against comparing its innovation data with earlier releases. That caution is about the innovation measures. This figure sits under business use of ICT, but the redevelopment is worth knowing before you lean on the size of the fall. If your business is in the 10%, the file your decisions come out of is worth more attention than the formula inside it. Start with whether anyone other than the person who built it can check the answer.
If you want the tool-level view rather than the formula-level one, our guide to Excel for business covers where Excel earns its place and where it stops.
How does VLOOKUP actually work?
VLOOKUP searches for a value in the first column of a range and returns a value from another column in the same row. It reads left to right only, and it counts the return column by position rather than by name. Those two constraints explain almost every complaint about it.
The syntax Microsoft documents is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
- lookup_value is what you are searching for.
- table_array is the range to search. The lookup value must sit in its first column, which Microsoft states as a requirement, not a preference.
- col_index_num is the column to return from, counted from the left-most column of table_array as column 1.
- range_lookup is the argument people leave off. Set it to FALSE for an exact match.
In practice that looks like =VLOOKUP(A2,Products!$A$2:$D$500,3,FALSE), which takes the product code in A2, finds it in column A of the Products sheet and returns the value from the third column of that range, column C.
The counting is what trips people up. If your range starts at column B, then column 3 is column D, not column C.
The range_lookup argument deserves its own warning. Microsoft’s VLOOKUP documentation states that when range_lookup is TRUE or omitted, Excel assumes the first column is sorted and searches for the closest value. That wording is looser than the behaviour. In practice it means the largest value at or below the one you asked for. Look up 10 in a list holding 9 and 10.5 and Excel returns 9, not the nearer 10.5.
Use absolute references on table_array so the range does not drift when the formula is filled down. Better still, point it at a named Excel table, which expands as rows are added so the range cannot fall behind.
What is the difference between VLOOKUP, XLOOKUP and INDEX/MATCH?
XLOOKUP does everything VLOOKUP does, in either direction, with a safer default. INDEX/MATCH does the same job with two functions instead of one and works in every version of Excel. VLOOKUP is the simplest of the three to read and the easiest to break. The right choice depends less on capability than on who else opens the file.
The table compares them on what decides the choice in a real workbook.
| Factor | VLOOKUP | XLOOKUP | INDEX/MATCH |
|---|---|---|---|
| Direction | Left to right only | Either direction | Either direction |
| Default match | Approximate unless you set FALSE | Exact | Approximate unless you set 0 |
| Return column | Counted by position, breaks if a column moves | Referenced directly, survives a moved column | Referenced directly, survives a moved column |
| Handles no match | Silent wrong value on the default, #N/A with FALSE | Text you supply via if_not_found, otherwise #N/A | Silent wrong value on the default, #N/A with match_type 0 |
| Available in | Every version of Excel | Microsoft 365, Excel 2021, Excel 2024 and the mobile apps | Every version of Excel |
| Reads easily to a non-expert, our view | Yes | Yes | No |
The last row is the one that matters most in a workbook someone else has to open. INDEX/MATCH is as flexible as XLOOKUP on paper and the only one besides VLOOKUP that runs in every version of Excel. It is also the hardest of the three to hand over, since two nested functions take longer to read than one. If the workbook has to be maintained by the next person in the role, that matters more than flexibility.
Which versions of Excel have XLOOKUP?
XLOOKUP runs in Microsoft 365, Excel 2021, Excel 2024 and the Excel mobile apps. It does not run in Excel 2016 or Excel 2019. That gap is the one argument for staying on VLOOKUP that does not rest on risk, and October 2025 changed what it is worth.
Microsoft’s XLOOKUP documentation is unambiguous: “XLOOKUP is not available in Excel 2016 and Excel 2019.” Microsoft’s 2025 end-of-support list gives both versions the same date, 14 October 2025, and the announcement for that date states the products involved no longer receive security updates, bug fixes or technical support.
So the sentence “some of the team are still on older Excel” has changed meaning. If older means Excel 2016 or Excel 2019, it describes an unsupported install, which makes it a security conversation with a deadline that has already passed.
| Excel version | XLOOKUP | Support status |
|---|---|---|
| Excel for Microsoft 365 | Yes | No end date |
| Excel LTSC 2024 | Yes | Support ends October 2029 |
| Excel 2021 and Excel LTSC 2021 | Yes | Support ends 13 October 2026 |
| Excel 2019 | No | Support ended 14 October 2025 |
| Excel 2016 | No | Support ended 14 October 2025 |
A perpetual licence is not what blocks XLOOKUP. Excel 2021 and Excel 2024 both have it, so the function gap sits only with the two versions that are out of support anyway. What a perpetual licence does carry is an expiry date. Microsoft’s configuration support matrix gives Office LTSC 2021 an end date of October 2026 and Office LTSC 2024 October 2029. Microsoft’s 2026 end-of-support list puts Excel 2021 and Excel LTSC 2021 at 13 October 2026. The same matrix also sets out what happens when the Windows underneath goes out of support, worth reading if any of these machines run Windows 10. Either way the live question is how long those machines stay supported, Office and Windows both, not which lookup function the workbook uses.
Why does VLOOKUP break and how would you know?
Four failures account for most of what goes wrong: a column inserted inside the range, approximate match left switched on, near-miss data throwing #N/A and cross-workbook links that break. The first two are silent, so you find them by checking instead of waiting for an error.
A column gets inserted and every result shifts. Someone adds a column inside the table, col_index_num keeps pointing at the same number and the formula starts returning the column next door. Nothing errors. The check takes seconds: confirm the column your formula counts to is still the one you meant.
Approximate match settles for the largest value at or below what you asked for. That is the right behaviour for a banded lookup, a tax table or a commission tier, as long as the first column is sorted ascending and holds the lower bound of each band. Everywhere else it is a fault, and the usual cause is that range_lookup was left off or set to TRUE, so Excel returns something close when you wanted something exact. Also silent, also plausible-looking. Sorting the source does not rescue it. On a sorted column Excel still returns that largest value at or below what you asked for, and errors only when nothing sits at or below it. On an unsorted column the answer is not predictable at all, because the search assumes an order that is not there. Test a handful of values you already know the answer to and, unless the lookup is deliberately banded, see whether they come back exact.
#N/A shows up when data almost matches. Trailing spaces, numbers stored as text, a code written two ways in two systems. Microsoft’s own best practices for VLOOKUP warn against leading and trailing spaces and against storing numbers or dates as text. At least this one is visible, as long as range_lookup is set to FALSE. The damage is that people work around it by hand, and the workaround becomes the process. Controlling entry at the source with Excel data validation prevents most of it.
Cross-workbook links break or crawl. Formulas that point at another file return the last value Excel cached while that file is closed, break when it is moved and can slow recalculation. Microsoft’s Excel performance guidance is blunt about the general case, saying to avoid inter-workbook links where possible because they can be slow, easily broken and not always easy to find and fix. Keeping linked files in one SharePoint or OneDrive location helps. Not linking at all helps more.
XLOOKUP fixes one of those four outright: it survives an inserted column, because it references the return column directly instead of counting to it. It also matches exactly by default, which you can already get for nothing by typing FALSE. It does nothing for near-miss data or broken links, which are data and architecture problems wearing a formula costume.
Is it worth rewriting the VLOOKUPs you already have?
Usually not, and not as a project in its own right. Rewrite the lookups you are already touching for another reason and leave the rest. A bulk find-and-replace across a working file is a large change with no visible benefit, and every change risks a fault that was not there before.
Work through it in this order.
- Find out what is actually wrong. If the complaint is wrong numbers, check whether the lookups are returning the wrong value or being fed the wrong value. Those have different fixes.
- Fix the structure before the formulas. Convert ranges to named Excel tables, split raw data from calculations, clean the source. Tables stop the range drifting as rows are added, and cleaning the source removes the near-miss failures, but none of that makes col_index_num safe.
- Set range_lookup to FALSE anywhere you want an exact match, which is almost everywhere. That is a targeted change, and it removes an entire silent failure mode without changing what a correct formula returns. Leave TRUE where the lookup is deliberately banded and its first column is sorted ascending.
- Rewrite only the formulas you are already changing. Anything you have to open and edit for another reason gets XLOOKUP. So does new work.
- Leave the ones that work. If a formula has been right for years and nobody has moved a column, the risk of the rewrite exceeds the risk of the formula.
One caveat before you write any XLOOKUP. Microsoft notes that a workbook built on a newer version can end up being opened in Excel 2016 or Excel 2019, where XLOOKUP is not available. Sort that out first.
The exception to the last two steps is a workbook changing hands. At that point readability stops being a preference and becomes the maintenance plan. VLOOKUP is easy to read but tedious to check, because verifying a positional column number means counting columns in a range you did not build. That is the one case where a bulk rewrite is worth the risk, and it is a handover job rather than a formula job. It is also the kind of decision we work through on spreadsheet solutions engagements.
What do you do when VLOOKUP cannot do the job?
Three situations push VLOOKUP past what it does well: returning a value from a column to the left of the lookup, matching on two or more criteria at once and summarising rather than retrieving. The first two are formula problems with formula answers. The third is a sign you are using the wrong tool entirely.
For a left-hand return, XLOOKUP handles it directly and INDEX/MATCH does the same in every Excel version. Neither needs you to rearrange your source columns first, which is the workaround older advice recommends.
For multiple criteria, the traditional fix is a helper column joining the values, then a lookup on the joined field. It works and it is easy to explain, which is why it is still the right choice in a workbook other people maintain. XLOOKUP and INDEX/MATCH can both do it without the helper column, at the cost of readability. Either way the formula is the smaller part of the job, and our Excel data analysis guide covers the workflow around it.
For the third case, stop. If you are looking up the same value hundreds of times to build a total, you want aggregation rather than retrieval, and our guide to pivot tables covers that properly. A lookup answers “what is the price for this product?” A pivot table answers “what sold, by category, by month?” Reaching for lookups to answer the second is how a workbook ends up slow enough that people stop opening it.
Frequently asked questions
Is VLOOKUP being removed from Excel?
No. VLOOKUP still works in current versions of Excel, including Microsoft 365, and Microsoft’s own documentation lists it as available in those versions. Newer functions have been added alongside it instead of replacing it, and files built on VLOOKUP keep working. There is no announced retirement date and no reason to plan around one.
What is the main difference between VLOOKUP and XLOOKUP?
VLOOKUP searches left to right and returns a column by counting its position, so inserting a column silently breaks it. XLOOKUP searches in either direction and references the return column directly, so an inserted or moved column does not affect it. XLOOKUP also matches exactly by default, while VLOOKUP returns an approximate match unless you tell it not to.
Why does my VLOOKUP return the wrong value instead of an error?
Usually one of two things. A column inserted inside the table range shifts what the column number points at, so the formula quietly returns the column next door. Or range_lookup was omitted or set to TRUE, so Excel falls back to the largest value at or below the one you asked for. Setting range_lookup to FALSE fixes that. Checking the column your formula counts to catches the first.
Can VLOOKUP look to the left?
No. VLOOKUP cannot return a value from a column to the left of the one it searches. XLOOKUP can return from either side. So can INDEX/MATCH, which works in every version of Excel. Rearranging your source columns to suit the formula is no longer necessary.
Which versions of Excel do not have XLOOKUP?
Microsoft states that XLOOKUP is not available in Excel 2016 or Excel 2019. Both of those versions reached end of support on 14 October 2025. Excel 2021, Excel 2024, Microsoft 365 and the Excel mobile apps all have it, so a perpetual licence on its own is not a barrier.
Should I rewrite all my old VLOOKUPs?
Rarely worth doing as a standalone project. Work out what has broken first, then fix the structure underneath it. Set range_lookup to FALSE anywhere you want an exact match, use XLOOKUP for new work and rewrite only the formulas you are already changing. The exception is a workbook being handed to a new owner, where readability becomes part of the handover.
Where to start
If a workbook is producing numbers you no longer trust, the first step is finding out whether the lookups are wrong or faithfully reporting bad data. That distinction decides whether you are looking at a half-hour fix or a rebuild, and you cannot tell from the outside.
Send us the workbook and we will tell you which one you have. That is where our spreadsheet solutions work begins.
