Why XLOOKUP Exists (and Why You Should Switch)
VLOOKUP has been in Excel since 1985, and almost everyone hates it. The complaints come up in every training session I run: 'Why does it only look right?', 'Why do I keep forgetting the FALSE?', 'Why does missing data blow up the whole column with #N/A?' Microsoft heard all of that, and XLOOKUP is the answer. If you already use VLOOKUP, this guide gives you a direct upgrade path. If you are new to lookups, start with XLOOKUP and skip the legacy syntax.
The three concrete wins over VLOOKUP. First, XLOOKUP can look in any column, not just the leftmost. Second, XLOOKUP defaults to exact match, so you do not need to remember the fourth argument. Third, XLOOKUP has a built-in 'not found' value, so you can decide what shows up when there is no match instead of returning #N/A. Those three changes save real time once you internalize them. The rest of the syntax is similar enough that any VLOOKUP you have already written translates directly.
If you are still on Excel 2019 or older, XLOOKUP is not available. For Office 2019 and earlier, INDEX/MATCH is the modern alternative that has the same advantages as XLOOKUP without needing the new function. For Microsoft 365, Excel 2021, Excel for the web, and Excel for Mac 2019+, XLOOKUP is built in and you should use it.
XLOOKUP Syntax in 30 Seconds
XLOOKUP has six arguments but only the first three are required, and the defaults match what you usually want. This is the syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). lookup_value is what you are searching for. lookup_array is the column or row to search in. return_array is the column or row to pull the answer from. That is the basic case, and it is shorter than VLOOKUP already.
=XLOOKUP(A2, B2:B100, D2:D100). Here A2 holds the value you want to find, B2:B100 is the column to search (say, employee ID), and D2:D100 is the column to return (say, employee name). Run this on the sample data and you get the employee name for the ID in A2. If A2 is empty or has a typo, by default XLOOKUP returns #N/A — the same as VLOOKUP without FALSE.
=XLOOKUP(A2, B2:B100, D2:D100, "Not found"). The fourth argument is a fallback value shown when the lookup fails. This is one of my favorite XLOOKUP features. Instead of seeing #N/A all over a report, you see a meaningful label like "Not found" or 0 or "—". You can also leave the cell blank with "" if you prefer a clean visual.
The same lookup in VLOOKUP is =VLOOKUP(A2, B2:D100, 3, FALSE). Notice three things XLOOKUP drops: you do not pick a column number (the third argument is a range, not a number), you do not specify the table with the lookup column on the left, and you do not need FALSE for exact match. Less to remember, fewer bugs.
When you port an existing VLOOKUP, the trickiest part is converting 'return column number' into 'return array'. If your VLOOKUP is =VLOOKUP(A2, Orders!A:F, 5, FALSE) (returns column E), the XLOOKUP is =XLOOKUP(A2, Orders!A:A, Orders!E:E). Notice the return is the whole column Orders!E:E, not E2:E1000. XLOOKUP works with whole columns, which is more robust when your data grows.
If you have never used VLOOKUP and just want the working syntax, you can stop at the three-argument form above. The not-found handler is what most beginners should add immediately, because it prevents ugly #N/A from cluttering reports. The match_mode and search_mode arguments cover edge cases (wildcards, binary search, last-to-first) that you can read about when you actually need them — they are not part of the everyday XLOOKUP you will write.
The Big Win: XLOOKUP Looks Left
VLOOKUP can only look right — the lookup column must be the leftmost column of the table you pass in. XLOOKUP does not care about column order, which removes one of the biggest frustrations in real work. If your lookup column is to the right of the return column, XLOOKUP handles it directly. With VLOOKUP you would have had to rearrange the table or rewrite the query with INDEX/MATCH.
Imagine you have two columns: A is Region and B is Salesperson. You want to find the salesperson for a given region. With VLOOKUP, since the lookup column (Region) is left of the return column (Salesperson), VLOOKUP works: =VLOOKUP("West", A2:B10, 2, FALSE). But if you flip it and want the Region for a given Salesperson, VLOOKUP fails outright — you cannot look right-to-left. XLOOKUP handles it: =XLOOKUP("Anna", B2:B10, A2:A10). That is the entire change.
XLOOKUP also makes two-way lookups trivial. If you have a matrix with months as columns and products as rows, and you want the value for a specific product and month, the formula is =XLOOKUP(product, A2:A10, XLOOKUP(month, B1:F1, B2:F2)). The inner XLOOKUP returns a row from the matrix; the outer XLOOKUP finds the right cell in that row. Once you see this pattern, you can read two-way lookups in any spreadsheet without re-deriving them every time.
The fact that XLOOKUP looks left is not just a convenience — it changes how you structure tables. With VLOOKUP, people would reorder columns to put the lookup column first, which often meant duplicating data across sheets. With XLOOKUP, you can keep your tables in a natural order and let the formula do the work. I recommend not reordering existing tables just to switch to XLOOKUP; the win is in the formula, not the table.
Five XLOOKUP Mistakes That Bite Everyone
XLOOKUP is simpler than VLOOKUP but it has its own quirks. Here are the five errors I see most often, and the one-line fix for each. None of these will error loudly — they will just return wrong numbers, so catching them on review is critical. Run through this list every time you write an XLOOKUP in a report that other people will rely on.
If you write =XLOOKUP(A2, B2:B100, D2:D50) the ranges are different lengths and Excel returns #VALUE!. The two arrays must have the same number of rows (or columns, for a horizontal lookup). The fix is to use whole-column references like B:B and D:D, or make sure the row numbers match exactly. Whole-column references are safer in my experience because they survive data growth.
When you drag an XLOOKUP down a column, the lookup_array shifts unless you anchor it with $. The fix is to write =XLOOKUP(A2, $B$2:$B$100, $D$2:$D$100) so the lookup and return arrays stay fixed. The lookup_value (A2) should stay relative so it advances row by row. This single $ pattern is the difference between a working and a broken lookup as soon as you copy the formula down.
XLOOKUP does not take a column number. If you write =XLOOKUP(A2, B2:D100, 3, "Not found") hoping to get the third column, you will get #VALUE! because 3 is not a valid return_array. The return argument is a range, not a number. If you are migrating from VLOOKUP, the mental shift is: instead of giving a column number, give the entire return column.
If the lookup_value has a trailing space and the lookup_array does not, XLOOKUP will not find a match even though the values look identical. This shows up constantly with data pasted from web pages or exports from other systems. The fix is to TRIM both sides: =XLOOKUP(TRIM(A2), TRIM(B2:B100), D2:D100). Even better, clean the source data once with Text.Trim in Power Query so the same problem does not haunt every formula.
XLOOKUP requires Excel 2021, Excel for Microsoft 365, or Excel for the web. If your colleague opens your file in Excel 2019, they will see #NAME? on every XLOOKUP cell. The fix is to save and share files as .xlsx knowing recipients may need a newer version, or use the IFS / INDEX / MATCH equivalents that work in older versions. Always check the Excel version on the receiving end before you standardize on XLOOKUP across a team.
Notice the pattern across these five mistakes: XLOOKUP does not error loudly on bad inputs. It returns the wrong number, an empty cell, or a #N/A. That is why review matters — a working XLOOKUP formula is not the same as a correct XLOOKUP formula. If your report feeds a downstream calculation, a bad lookup will silently corrupt everything downstream. Add the COUNTIF sanity check I described above and you will catch most of these before they ship.
Before you trust an XLOOKUP, sanity-check with COUNTIF. If =COUNTIF(lookup_array, lookup_value) returns 1, you expect XLOOKUP to find exactly one match. If it returns 0, XLOOKUP returns your 'not found' message. If it returns more than 1 and you did not set match_mode, you get the first match — which may be wrong if your data has duplicates. This COUNTIF pre-check catches more lookup bugs than any other habit.
XLOOKUP vs VLOOKUP vs INDEX/MATCH: Which to Use
If you are starting fresh or have Microsoft 365, use XLOOKUP — it is the modern default and there is no reason to learn the older syntax. If you maintain workbooks in Excel 2019 or older, INDEX/MATCH is the modern alternative that works the same way (and looks left). VLOOKUP still works, and you will meet it in legacy files, but for new formulas I recommend XLOOKUP. Below is the decision rule I follow when reviewing other people's spreadsheets.
For any new workbook in Microsoft 365, Excel 2021, or Excel for the web, write =XLOOKUP(lookup_value, lookup_array, return_array, "Not found"). This is shorter than VLOOKUP, looks left, defaults to exact match, and has a clean not-found handler. There is almost no reason to use VLOOKUP in a new file unless your team's standard is to stay on legacy syntax.
=INDEX(return_array, MATCH(lookup_value, lookup_array, 0)). This works in every Excel version back to 2007. It looks left, defaults to exact match (the 0 in MATCH), and is just as flexible as XLOOKUP. The syntax is longer but it is the right choice when you must support older Excel versions. INDEX/MATCH is also what to teach in courses aimed at corporate users stuck on older Office.
If a workbook already has 50 VLOOKUPs, do not rewrite all of them just to switch functions. The risk of breaking something is higher than the benefit. Add XLOOKUP for new formulas and leave the existing VLOOKUPs alone until the workbook is rebuilt for another reason. Mixing VLOOKUP and XLOOKUP in the same file is fine — both work, and Excel does not care.
One more thing to consider: XLOOKUP returns a single value, so for column-or-row returns you may need to wrap it. If you want to return multiple columns based on a single lookup, write =XLOOKUP(A2, B2:B100, D2:F100) and Excel spills the result across three columns automatically (this is dynamic-array behavior in Microsoft 365). That is one of the cleanest upgrades over VLOOKUP, which returns only a single column per call.
If you are writing a formula in a shared workbook and you are unsure which Excel version the team uses, ask before you choose XLOOKUP. I have walked into meetings where the presenter opens a file full of XLOOKUPs and half the audience sees #NAME? errors. The compatibility risk is real. When in doubt, use INDEX/MATCH — it has worked everywhere since 2007 and will not surprise your audience.
Practice These Three Lookups
Below are three practice lookups using the same small employee table, designed to build from the simple case to the two-way lookup. Do them in order on a copy of the table so you can see exactly what XLOOKUP does at each step. The point of the practice is to make the syntax automatic — once it is, you stop reaching for VLOOKUP out of habit and the new syntax becomes your default.
Given an employee ID in cell A2, return the employee name from column D. Write =XLOOKUP(A2, B2:B100, D2:D100, "Not found"). Change A2 to a valid ID and confirm you get the name. Change A2 to an ID that does not exist and confirm you get "Not found" instead of #N/A. This is the version you will write 90% of the time.
Given a salesperson name in A2, return the region they cover from column A. Write =XLOOKUP(A2, C2:C100, A2:A100, "Not found"). This would have been impossible with VLOOKUP because the lookup column (C, salesperson) is to the right of the return column (A, region). Notice how clean it is in XLOOKUP — the function does not care about column order at all.
Given a product name in A2 and a month in B2, return the sales figure from a matrix with products in column A and months in row 1. Write =XLOOKUP(A2, G2:G10, XLOOKUP(B2, H1:N1, H2:N10), "Not found"). The inner XLOOKUP returns the product's row from the matrix; the outer XLOOKUP pulls the value for the given month. This is the pattern that used to require a complex INDEX/MATCH/MATCH formula, and XLOOKUP makes it readable.
If you do not have a sample dataset handy, build a tiny one in a new sheet. Create a 5-row employee table with columns A=Region, B=EmployeeID, C=Name, D=HireDate. Then try the three practice formulas above. Five minutes of practice beats an hour of reading — XLOOKUP clicks once you see the cell populate with the right value.
