Technology

XLOOKUP vs VLOOKUP: Which Excel Lookup Should You Use in 2026?

Daylongs ·
#Excel #XLOOKUP #VLOOKUP #Excel Formulas #INDEX MATCH #Lookup Functions #Microsoft 365 #Spreadsheets

XLOOKUP and VLOOKUP both pull a value out of a table based on a key, but they search differently. VLOOKUP looks only in the leftmost column and returns a value by column number, while XLOOKUP lets you pick the search column and the return column separately.

This guide runs both functions against one small sample table, so you can see the formulas and results side by side. It covers syntax, not-found handling, left lookups, multiple columns, horizontal tables and the thing that actually trips people up at work: file compatibility. Versions and behavior are as of November 2026 for Microsoft 365 and desktop Excel 2021 and later; the examples assume a US-style setup where arguments are separated by commas.

What is the difference between XLOOKUP and VLOOKUP?

XLOOKUP separates “where to search” from “what to return,” defaults to an exact match, and has a built-in slot for a not-found message. VLOOKUP ties the search to the first column of a range and returns by counting columns.

VLOOKUP’s design is old, and it shows. The key has to sit in the first column of the range you select. The result is chosen by a number, such as 3 for “the third column in that range.” And if you leave off the last argument, Excel quietly does an approximate match, which on an unsorted list can return a wrong row without any error.

XLOOKUP fixes each of those in one move. The table below is the quick comparison.

FeatureVLOOKUPXLOOKUP
SyntaxVLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Where the key can beFirst column of the range onlyAny column or row
How the result is chosenColumn numberA range you select
Default matchApproximate (TRUE) if the last argument is omittedExact
When nothing matches#N/A unless wrapped in IFERRORFourth argument returns your text
Multiple return columnsOne formula per columnOne formula can return several
Horizontal tablesNeeds HLOOKUPSame function
Last matchNot directlysearch_mode of -1
AvailabilityEvery Excel versionMicrosoft 365, Excel 2021 and later

Both functions are documented by Microsoft: see its XLOOKUP function page and VLOOKUP function page. A note on sourcing: I confirmed the syntax and the version statement through search results that quote those pages, but I could not open the full pages while writing, so check the argument descriptions there before you build anything critical.

How do the two formulas look on the same data?

Here is the sample table used for the rest of this article. It is a small price list, with product IDs in column A.

ABCD
1Product IDProductPrice (USD)In stock
2P-101Pen1.20340
3P-102Notebook3.5085
4P-103Folder2.200
5P-104Marker1.80120
6P-105Stapler6.9042

Say F2 contains P-103 and you want the price. Here are the two versions.

=VLOOKUP(F2,$A$2:$D$6,3,FALSE)
=XLOOKUP(F2,$A$2:$A$6,$C$2:$C$6)

Both return 2.20. The difference is in how you read them a month later. VLOOKUP says “the third column of that block,” so you count A, B, C on your fingers. XLOOKUP says “find it in A, give me C,” and both ranges are visible in the formula.

The dollar signs lock the ranges so they do not drift when you copy the formula down. If a VLOOKUP that works in row 2 breaks in row 10, the missing anchors are the usual cause, and our VLOOKUP #N/A troubleshooting guide walks through that and the other common causes.

How do you use XLOOKUP, argument by argument?

XLOOKUP takes up to six arguments, but only the first three are required. Start with those and add the others when a situation calls for them.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
ArgumentWhat it doesIf you skip it
lookup_valueThe thing you are searching for, such as F2Required
lookup_arrayThe column or row to search inRequired
return_arrayThe column or row to return fromRequired
if_not_foundWhat to show when nothing matches#N/A
match_mode0 exact; -1 exact or next smaller; 1 exact or next larger; 2 wildcard0 (exact)
search_mode1 first to last; -1 last to first; 2 or -2 binary search1

One rule saves a lot of grief: the lookup array and the return array must be the same size. If one is A2:A6 and the other is C2:C7, the result will be wrong or an error. Whole-column references such as A:A and C:C sidestep the mismatch, though on very large workbooks they can slow recalculation.

In practice, most real formulas are the first three arguments plus a not-found message. Learn that shape and you have covered most of what people use XLOOKUP for.

How do you handle a value that is not found?

Use the fourth argument. XLOOKUP’s if_not_found slot replaces the IFERROR wrapper you would otherwise put around VLOOKUP.

Suppose a short order list in column F holds P-103, P-105 and P-109, and P-109 is not in the price table.

=XLOOKUP(F4,$A$2:$A$6,$C$2:$C$6,"Not found")
ID in column FVLOOKUP with FALSEXLOOKUP with “Not found”
P-1032.202.20
P-1056.906.90
P-109#N/ANot found

There is a real difference between this and =IFERROR(VLOOKUP(…),“Not found”). IFERROR catches every error type, including #REF! from a deleted column and #VALUE! from a bad argument. If you mistype a range, your sheet will cheerfully say “Not found” for every row, and you will not notice until someone asks why every order is missing. The if_not_found argument responds only to a missing match, so structural mistakes still announce themselves.

Sometimes you want the raw #N/A, because it is a visible flag that a key is missing from the master list. In that case leave the argument out, and treat the #N/A as a to-do item rather than something to hide.

Can XLOOKUP look left and return several columns at once?

Yes to both. Since the search range and the return range are independent, direction does not matter, and a multi-column return range spills its results into neighboring cells.

First, a left lookup. You know the product name, “Marker,” and want its ID, which sits to the left in column A.

=XLOOKUP("Marker",$B$2:$B$6,$A$2:$A$6)

The answer is P-104. With VLOOKUP you would have had to rearrange the table, build a helper range or switch to INDEX and MATCH.

Now several columns. For P-103 you want the name, price and stock in one go.

=XLOOKUP(F2,$A$2:$A$6,$B$2:$D$6)

Enter this in G2 and Excel fills G2 with Folder, H2 with 2.20 and I2 with 0. That spreading behavior is called a spill. If H2 or I2 already holds data, Excel shows #SPILL! until you clear the way. You end up maintaining one formula instead of three, which matters when someone later changes the range.

A caution for report templates: spilled results cannot be partly deleted or individually formatted as easily as ordinary cells. If you are filling a rigid form where each cell has its own formatting, single-value formulas are often easier to live with.

Does XLOOKUP work with horizontal tables, ranges and wildcards?

It does, and this is where the match_mode argument earns its keep. The same function reads rows, finds bands such as tax brackets or grades, and matches partial text.

A horizontal table first. Monthly sales sit in a row.

ABCDE
1ItemJanFebMarApr
2Sales120150170140
=XLOOKUP("Mar",$B$1:$E$1,$B$2:$E$2)

The result is 170, with no HLOOKUP to remember.

For band lookups, match_mode does the work. Here is a grading scale.

AB
1Minimum scoreGrade
20F
360D
470C
580B
690A
=XLOOKUP(85,$A$2:$A$6,$B$2:$B$6,,-1)

A match_mode of -1 means “exact match, or the next smaller value.” No row holds 85, so Excel falls back to 80 and returns B. Notice the empty slot between the commas: that is the skipped if_not_found argument. The same idea fits commission tiers, shipping weight bands and tax brackets, with the tier thresholds in the first column.

Wildcards use match_mode 2, with an asterisk for any run of characters and a question mark for a single one.

=XLOOKUP("*en",$B$2:$B$6,$C$2:$C$6,,2)

That matches the first product whose name ends in “en,” which is Pen, and returns 1.20. Partial matching is handy but blunt: if several rows qualify, you only get the first, so do not treat it as a unique identifier.

What if the same key appears more than once?

XLOOKUP returns the first match from the top by default. Set search_mode to -1 to get the last one instead.

This shows up with transaction logs, where the same customer or part number appears many times and you want the most recent entry, assuming newer rows are added at the bottom.

=XLOOKUP(F2,$A:$A,$C:$C,,0,-1)

Count the arguments: the fourth (if_not_found) is left blank, the fifth (match_mode) is 0, and the sixth (search_mode) is -1. VLOOKUP has no direct equivalent, and people used to build helper columns or array formulas.

Two-condition lookups are possible too, by multiplying logical tests so that only rows meeting both conditions equal 1.

=XLOOKUP(1,($A$2:$A$100=G1)*($B$2:$B$100=G2),$C$2:$C$100)

That works on current versions of Excel, but it is the point where readability starts to suffer. If what you really need is a total rather than a single retrieved value, a conditional sum is clearer, and our SUM, SUMIF and SUMIFS guide covers that case.

Which Excel versions support XLOOKUP?

XLOOKUP is available in Microsoft 365, Excel 2021 and Excel 2024. Microsoft’s support article states it is not available in Excel 2016 or Excel 2019.

One wrinkle: that same article’s “applies to” list shows some older version names, which seems to contradict the body text. When a vendor’s page disagrees with itself, trust the specific statement in the text, and when you can, test it directly by typing =XLOOKUP in the version you actually use. If the function name does not autocomplete, you do not have it.

What happens on an older version is the part people underestimate. A workbook built in a current version opens in Excel 2019, but the XLOOKUP cells show #NAME?, because the older program does not recognize the function name.

Here is a failure case. It is a composite of situations that come up constantly in offices, not a specific incident.

A finance lead builds a month-end pricing workbook in Microsoft 365, using XLOOKUP to pull unit costs. She emails it to two vendors. One of them runs Excel 2019. His price column turns into a wall of #NAME? errors, he assumes the file is corrupted, and he retypes the costs by hand from a PDF, overwriting the formulas. The next month, the numbers in the two vendors’ copies no longer agree with the master.

The lesson is not that XLOOKUP is risky. It is that you should ask what version the recipient has before sending a formula-heavy file, or send values instead: copy the range and use Paste Special, Values. Inside a company where IT has standardized on current Office, you can skip the worry and just confirm once.

If you share through the web, Google Sheets is generally understood to offer XLOOKUP as well, but I did not verify that for this article, so test it in your own sheet before relying on it.

What is the backward-compatible alternative: INDEX and MATCH?

INDEX with MATCH is the long-standing answer. It runs on much older Excel versions and handles left lookups.

=INDEX($C$2:$C$6,MATCH(F2,$A$2:$A$6,0))

MATCH finds which position F2 holds in the ID column, and INDEX returns the item at that position from the price column. The trailing 0 asks for an exact match. For P-103 it returns 2.20, same as the other two formulas.

Choosing between the three comes down to who opens the file.

SituationBest choice
A workbook only you use, on Microsoft 365XLOOKUP
Whole team on Excel 2021 or laterXLOOKUP
File going to clients, vendors or unknown versionsINDEX and MATCH, or paste values
An existing workbook already built on VLOOKUPLeave it, replace only what breaks
Anyone on Excel 2019 or earlier in the loopVLOOKUP or INDEX and MATCH

Resist the urge to convert a whole library of working VLOOKUPs just for tidiness. A formula that works is not a bug. Switch the ones that keep breaking, the ones that need a left lookup, and everything you write from scratch. If you want a map of where lookups sit among the other function families, the Excel functions cheat sheet lays them out by job.

Why does VLOOKUP fail silently, and does XLOOKUP fix it?

VLOOKUP’s column-number design can return the wrong column without any error, and XLOOKUP largely removes that risk because it points at an actual range.

Say your formula is =VLOOKUP(F2,$A$2:$D$6,3,FALSE), returning price from column C. A colleague inserts a “Size” column between Product and Price. The table range stretches to include it, but the number 3 still means “third column,” which is now the new Size column, not Price. The formula keeps returning values, now the wrong ones, with no warning, and the numbers may travel into a summary report before anyone looks.

With XLOOKUP, the return array is a real column reference. Insert a column and Excel moves the reference along with the data it points to, so the formula keeps returning prices. That is a practical benefit beyond style.

XLOOKUP is not magic, though. Both functions fail the same way on dirty data:

  • A number stored as text on one side will not match a true number on the other. The ID “1001” typed as text is not 1001.
  • Leading or trailing spaces make two values that look identical different. TRIM cleans them.
  • Matching is not case-sensitive, so “abc” and “ABC” count as the same key.

Clean data comes before function choice. If the workbook itself has grown heavy and is lagging, a general fix such as the steps in our slow computer guide may help more than any formula rewrite.

Which should you learn first?

If you are new to Excel today, learn XLOOKUP first, and learn to read VLOOKUP anyway. Both will cross your desk.

Older workbooks are full of VLOOKUP, and you will inherit them. Being able to read one means you can fix it, explain it and offer a compatible version to someone on older Excel. You do not need to carry the habit of counting columns into new files.

The order I would follow: get comfortable with XLOOKUP’s three arguments and the not-found slot, write one INDEX and MATCH by hand so you understand what is under the hood, then read a few VLOOKUPs until they stop looking strange. If your basics are still shaky, 10 Excel formulas everyone should know is a better starting point. Once lookups feed a summary, the PivotTable beginner’s guide is the natural next step, and the shortcuts in the keyboard shortcuts guide, F4 for locking references in particular, will make the whole job faster.

One last habit worth building: before you send any workbook with formulas to someone outside your team, ask which Excel they use. That single question costs a minute and saves the back-and-forth that comes from a sheet full of #NAME?.

This article is general information, not professional advice. Menu names and behavior vary by Excel version and platform, and the facts here reflect November 2026. Microsoft can change function availability, so confirm against its official support pages before relying on a formula in an important file. The failure case above is a composite example, not a report of a real incident.

What is the difference between XLOOKUP and VLOOKUP?

VLOOKUP searches only the leftmost column of a table and returns a value by column number. XLOOKUP takes a lookup range and a separate return range, so the key can sit in any column, and it defaults to an exact match. The trade-off is availability: XLOOKUP needs Microsoft 365, Excel 2021 or Excel 2024.

How do I use XLOOKUP in Excel?

The basic form is =XLOOKUP(lookup_value, lookup_array, return_array). For example, =XLOOKUP(F2,$A$2:$A$6,$C$2:$C$6) looks for the ID in F2 inside column A and returns the matching price from column C. Optional fourth to sixth arguments set a not-found message, the match mode and the search direction.

How do I make XLOOKUP show a message instead of #N/A?

Fill in the fourth argument, if_not_found. =XLOOKUP(F2,$A$2:$A$6,$C$2:$C$6,"Not found") returns the text when there is no match. Unlike IFERROR, it reacts only to a missing match, so a broken range or a type problem still shows its real error instead of being hidden.

Does XLOOKUP work in Excel 2019?

No. Microsoft's support article says XLOOKUP is not available in Excel 2016 or Excel 2019, and it is available in Excel 2021, Excel 2024 and Microsoft 365. If a coworker on Excel 2019 opens a workbook that uses it, expect #NAME? errors where the formulas were.

Is VLOOKUP obsolete?

No. VLOOKUP still works in every current version, and nothing in Microsoft's documentation says it is being removed. It remains the safe choice when the file goes to people on older Excel. For new workbooks that only Microsoft 365 or 2021-and-later users will open, XLOOKUP is the easier tool.

Can XLOOKUP return more than one column?

Yes. Point the return array at several columns, such as =XLOOKUP(F2,$A$2:$A$6,$B$2:$D$6), and the results spill across adjacent cells. The cells to the right must be empty, otherwise Excel shows a #SPILL! error.

Can XLOOKUP look to the left of the lookup column?

Yes, because the lookup and return ranges are independent. =XLOOKUP("Marker",$B$2:$B$6,$A$2:$A$6) finds the name in column B and returns the ID from column A. VLOOKUP cannot do this directly, which is why people turned to INDEX with MATCH.

How do I get the last match instead of the first with XLOOKUP?

Set the sixth argument, search_mode, to -1 so Excel searches from the bottom up. A formula like =XLOOKUP(F2,A:A,C:C,,0,-1) returns the last row that matches. Leave the fourth argument blank and keep the match mode at 0 for an exact match.

Should I use XLOOKUP or INDEX and MATCH?

XLOOKUP has the simpler syntax. INDEX with MATCH works in much older versions of Excel, so it is the safer pick for files that go to clients, vendors or anyone whose version you cannot confirm. For a workbook you control, use XLOOKUP.

공유하기

관련 글