Blog

Excel XLOOKUP function is a game-changing lookup tool that every Excel user should know. Introduced in the latest versions of Excel (Office 365 and Excel 2021), XLOOKUP is often called the “successor” or even the “VLOOKUP killer” for its ability to overcome many limitations of the older lookup functions. Whether you’re an Excel beginner struggling with your first lookup formula or an advanced user curious about upgrading from VLOOKUP or INDEX/MATCH, this comprehensive guide has you covered. We’ll explain what XLOOKUP is, how to use it (with clear syntax and examples), and compare XLOOKUP vs VLOOKUP vs INDEX/MATCH to highlight why this new function has become a go-to for Excel pros.

Why pay attention? If you’ve ever been frustrated by #N/A errors or the rigid rules of VLOOKUP (like needing the lookup value in the first column), XLOOKUP is here to make your life easier. I remember the first time I tried XLOOKUP – it felt refreshingly simple and powerful. Honestly, after using it in a project, I wondered how I survived without it! 🙂 In this guide, I’ll also share a few personal tips on where XLOOKUP shines, because I’ve been there, learning these tricks myself.

A note for our readers: This post is part of our Excel Pro Tutorial series, where we aim to make Excel and data analysis easy for everyone. On our website, you’ll find more great articles on Excel, Power BI, SQL, Python, ChatGPT automation, and more. Now, let’s dive into XLOOKUP and boost your Excel skills. 🚀

What is the Excel XLOOKUP function?

The XLOOKUP function is Excel’s modern solution for lookup needs. In simple terms, XLOOKUP finds a value in one column (or row) and returns a corresponding value from another column (or row) – without the many restrictions older functions had. It was introduced in Excel for Office 365 and Excel 2021 as a more powerful, flexible replacement for functions like VLOOKUP and HLOOKUP (and even the combination of INDEX+MATCH)m. XLOOKUP can search both vertically and horizontally, making it a single function that effectively replaces VLOOKUP (vertical lookup) and HLOOKUP (horizontal lookup) in one go.

Some quick facts about XLOOKUP:

• Availability: XLOOKUP is available in Excel 365, Excel 2021 and later versions (including Excel for the Web). It is not available in older perpetual versions like Excel 2016 or 2019. So if you try to use XLOOKUP in an older Excel, you’ll get a #NAME? error because it won’t recognize the function.
• Purpose: Just like VLOOKUP was used to find something in a table by row, XLOOKUP does the same job with more flexibility. You can look up a value in one column and return a result from any column to the left or right of it (VLOOKUP couldn’t look left), or even perform horizontal lookups by rows – all with one function.
• Why It’s Special: XLOOKUP addresses several limitations of VLOOKUP/HLOOKUP. For example, VLOOKUP could only search the leftmost column, but XLOOKUP can search any column or row. VLOOKUP required counting column index numbers (which could break when columns are inserted); XLOOKUP directly references return ranges, making formulas more robust. We’ll explore all these differences in detail in the comparison section.

In essence, XLOOKUP is designed to be a one-stop lookup solution for modern Excel users. Microsoft themselves describe XLOOKUP as a function that “finds things in a table or range by row… regardless of which side the return column is on”. That means no more worrying about the lookup column needing to be first, or writing clunky combinations of formulas to get results from the left side of your dataset. If you have a newer version of Excel, XLOOKUP is definitely something to start using – it can simplify your spreadsheets and reduce errors.

How to Use Excel XLOOKUP function: Syntax and Arguments

Before we compare XLOOKUP with other functions, let’s understand how XLOOKUP works. The syntax of XLOOKUP might look a bit long at first, but it’s quite logical once you break it down:

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Let’s explain each part:

• lookup_value – The value you are searching for. This could be a number, text, cell reference, etc. For example, “Alice” if you’re looking up an employee name, or A2 if the value to find is in cell A2.
• lookup_array – The range (column or row) where you want to search for the lookup_value. This is basically the list of values that XLOOKUP will scan to find a match. It can be a vertical range (like A2:A10) or a horizontal range (like A1:E1), depending on your data.
• return_array – The range (column or row) from which you want to return a result. This should correspond in size to the lookup_array (e.g., if lookup_array is A2:A10, the return_array might be C2:C10 if you want to return values from column C). XLOOKUP will return the value from this range that aligns with the found lookup_value.

The above three are the required arguments – with these, you can do a basic lookup. But XLOOKUP offers three more optional arguments that add a lot of power:

• [if_not_found] – (Optional) This is a value (or text) to return if the lookup_value isn’t found in the lookup_array. If you omit this, XLOOKUP will return the standard #N/A error when it can’t find a match. But if you provide an if_not_found value, you can display a custom message or value instead of an error. For example, you could put “Not found” here, or even a blank ("") to return an empty cell. This is much easier than wrapping your lookup in an IFERROR or IFNA function – a trick we had to do with VLOOKUP to handle missing values.
• [match_mode] – (Optional) This tells XLOOKUP how to match the lookup_value with values in the lookup_array. The default is 0 – Exact match – which means XLOOKUP will look for an exact match and if none is found, it will return an error (or your [if_not_found] value, if provided). Other options include -1 (exact match or next smaller item), 1 (exact match or next larger item), and 2 (wildcard match). These options let you do approximate matches (useful for finding the next closest number, like tax brackets or grades) and wildcard searches (where * can represent any sequence of characters, etc.). Notably, XLOOKUP defaults to exact match (0) if you don’t specify match_mode. This default is more intuitive and safer, since the old VLOOKUP defaulted to approximate match which often caused accidental errors if you forgot to put FALSE for exact match.
• [search_mode] – (Optional) This controls the order and method of search. The default is 1 (search from first to last, i.e., top to bottom in a vertical range). If you set it to -1, XLOOKUP will search from last to first (bottom to top). This is incredibly useful if you want the last occurrence of a value instead of the first. Other options are 2 (binary search on sorted data ascending) or -2 (binary search on sorted data descending). Binary search is an advanced feature that can speed up lookups on large sorted datasets, but be careful: your data must be sorted for this to work correctly, otherwise you’ll get wrong results. For most cases, you’ll use the default linear search (which is plenty fast for typical-sized data). The ability to search backwards (last-to-first) is something no built-in function offered before – you’d have to do some fancy tricks with INDEX/MATCH or helper columns – so this is a welcome addition.

Excel XLOOKUP function Syntax Example

To make this concrete, let’s look at a quick example of a basic XLOOKUP formula in action:

Suppose you have a small table of products and their prices:

If you want to find the price of “Banana” using XLOOKUP, you would write:

=XLOOKUP("Banana", A2:A4, B2:B4)

Here, "Banana" is the lookup_value, A2:A4 is the lookup_array (the Product column), and B2:B4 is the return_array (the Price column). Excel will look through A2:A4, find “Banana” in A3, then return the value from B3 (which is $0.50).

Now, if we tried to do the same with a traditional VLOOKUP, we’d have to ensure the lookup column is the first column in the table. In the above example, it is (Product is the first column, so VLOOKUP would actually work fine). However, imagine if the table columns were reversed – say we had Price in the first column and Product in the second column, and we wanted to look up by product name. VLOOKUP wouldn’t be able to do it directly because it can only search the first column. XLOOKUP, on the other hand, doesn’t care which column is where: you explicitly tell it where to search and where to retrieve the result from. We’ll explore this “lookup in any direction” feature more in the next section.

Before moving on, let’s highlight one more neat feature: returning multiple values. XLOOKUP can return an array of results if your return_array spans multiple columns. For example, imagine your table has three columns: Product ID, Product Name, and Price. If you want to retrieve both the Name and Price by looking up the ID, XLOOKUP can do that in one formula! You’d set the lookup_array as the ID column and the return_array as both the Name and Price columns. The result will “spill” into two cells (one for Name, one for Price). In older Excel, you’d need two separate lookups to get two columns of info, but XLOOKUP can pull back a whole row of data in one go. This works because XLOOKUP returns a reference to the return range, not just a single value. Very cool, right?

Now that we know how to use XLOOKUP and what each part of the syntax means, let’s compare it to the classic functions it’s replacing. Understanding the differences between XLOOKUP vs VLOOKUP (and vs INDEX/MATCH) will not only solidify why XLOOKUP is so powerful, but also help you appreciate situations where the old methods might still come in handy.

XLOOKUP vs VLOOKUP: Key Differences and Advantages

If you’ve been using Excel for a while, chances are you’ve used or at least heard of VLOOKUP. It’s one of Excel’s most famous functions for retrieving data. However, VLOOKUP comes with several well-known limitations and quirks. XLOOKUP was built to address those pain points. Let’s break down the key differences and why XLOOKUP is often a better choice:

1. Lookup Direction (Left vs Right): Perhaps the biggest limitation of VLOOKUP is that it can only search for the lookup value in the leftmost column of the table array. This means your data has to be organized with lookup keys on the left. If your lookup key is not the first column, VLOOKUP simply won’t work (without rearranging your columns or using a different approach). XLOOKUP removes this restriction. With XLOOKUP, you specify the lookup range and the return range independently. The lookup column can be to the left, right, or anywhere – XLOOKUP doesn’t care. You could have your IDs on the rightmost column and still lookup an ID to return a name from the left side. This flexibility is huge when working with real-world data that isn’t always in neat left-to-right order.
   • Example: Suppose you have a table where Column A is “Price” and Column B is “Product”. If you want to find the price of a given product, VLOOKUP can’t do VLOOKUP(product, A:B, 1, FALSE) because it expects the product name to be in column A (leftmost). XLOOKUP can do XLOOKUP(product, B:B, A:A) and happily find the price in column A even though it’s to the left of the product name column. No more rearranging data for lookups!
2. Specifying the Return Column (Index vs Range): In VLOOKUP, once you specify the table, you have to give the column index number of the value you want to retrieve. For instance, VLOOKUP(ID, table, 3, FALSE) means “find the ID in the first column of table, then go to the 3rd column of that table to get the result.” This approach is problematic because if you insert or delete columns in between, that index number can change and the formula might break or return wrong data. Also, VLOOKUP can only return one value at a time (one column). XLOOKUP, in contrast, asks for a return range (return_array) instead of a column number. You highlight the exact range of the results you need. This makes formulas more robust to structural changes in your sheet. If you add a new column to your data, XLOOKUP will still be pointing to the correct return range (since it’s tied to the range reference, not a number index). Moreover, as mentioned earlier, XLOOKUP can return multiple columns of data in one go by simply specifying a multi-column range as the return_array. VLOOKUP would require multiple functions or a cumbersome {INDEX, MATCH} construction to achieve the same.
3. Default Match Behavior: By default, VLOOKUP assumes an approximate match (it treats the range_lookup argument as TRUE if you omit it). This means if it doesn’t find an exact match, it will return the next closest lower value if your data is sorted – which can lead to incorrect results if you weren’t expecting it. In practice, most of us use VLOOKUP(..., FALSE) to force an exact match to avoid that headache. XLOOKUP, on the other hand, defaults to exact match. If you don’t specify a match_mode, it will behave as if you put 0 (exact match). This is safer for new users and prevents those accidental wrong matches. XLOOKUP can still do approximate matches when you want (using match_mode 1 or -1), but you’ll have to explicitly set that, and it doesn’t require sorting the data in ascending order unless you opt for the binary search mode. In short: XLOOKUP’s defaults are more sensible and user-friendly.
4. Handling Missing Values (Errors): When VLOOKUP cannot find what you’re looking for, it returns the infamous #N/A error. Many Excel users then wrap their VLOOKUP in an IFERROR or IFNA to catch that and display something more user-friendly (like “Not found” or 0). XLOOKUP simplifies this by providing the [if_not_found] argument. You can decide what XLOOKUP should return if there’s no match – a custom message, a default value, or even blank. This is built-in error handling that VLOOKUP lacks. For example, XLOOKUP("Orange", A2:A4, B2:B4, "Not in list") might return “Not in list” if “Orange” isn’t found, whereas VLOOKUP("Orange", A2:B4, 2, FALSE) would just throw #N/A (unless you added IFERROR around it). The result: your spreadsheets can be more user-friendly and require less formula nesting to handle simple “not found” cases. VLOOKUP vs XLOOKUP error handling: In the image above, we attempted to lookup “Watermelon” which doesn’t exist in the list. VLOOKUP returns #N/A, leaving the user guessing. XLOOKUP, with an if_not_found message provided, returns a friendly message (“Oops, nothing is found :(”) instead. This illustrates how XLOOKUP’s built-in error handling can make your reports cleaner and less confusing for others.
5. Search Options (First vs Last, Binary search): VLOOKUP always searches from the first record downwards and stops at the first match it finds. If your data has multiple possible matches (duplicate keys), VLOOKUP has no simple way to fetch the last occurrence or nth occurrence of a value. XLOOKUP introduces the search_mode parameter which allows reverse search (last-to-first) with a simple -1 setting. This means if you want the last entry for a certain ID or name, XLOOKUP can get it without any array tricks or helper columns. Additionally, XLOOKUP can perform binary searches (2 or -2 for search_mode) for super-fast lookups on sorted data. VLOOKUP has no equivalent of searching from bottom or binary search (other than manually tweaking the data or using other functions). In practice, the reverse search is incredibly handy for things like the most recent transaction for a customer, the latest date entry, etc. Search order difference: Here we see a comparison where we want to find the first and last orders for a given item. VLOOKUP can only get the first match (e.g., first order of “Apples” = supplier Liam) and cannot directly retrieve the last match. XLOOKUP easily returns both: by default the first match (Liam), and with search_mode = -1 the last match (Owen) for “Apples”. This ability to search from bottom as well as top is a unique advantage of XLOOKUP.
6. Column Insertions & Table Flexibility: As hinted earlier, VLOOKUP formulas are prone to breaking or returning wrong data if you insert a new column in the table – because the col_index_num you hard-coded might now be pointing to a different column. XLOOKUP is immune to this issue since it doesn’t rely on index numbers but actual range references. This makes XLOOKUP formulas more robust to changes in your worksheet structure. If you’re someone who continuously updates or modifies your data layout, XLOOKUP will save you a lot of headaches.
7. Character Limitations: This is a more technical difference, but worth noting for advanced users: VLOOKUP has a known limitation where the lookup_value cannot exceed 255 characters (if it does, VLOOKUP will return an #VALUE! error or behave unexpectedly). XLOOKUP does not have this 255-character limit for lookup values. While typical lookup values (names, IDs, etc.) aren’t that long, if you ever deal with very long strings or IDs, XLOOKUP will handle them where VLOOKUP fails.
8. Multiple Criteria Lookups: VLOOKUP by itself can’t handle multiple criteria (for example, lookup based on two columns match, like find a value where both Name and Department match). We often had to use tricks like concatenating fields or using INDEX/MATCH for that. XLOOKUP, while not having a direct multi-criteria parameter, can handle multiple criteria through creative use of arrays or logical operations in the lookup_array. For instance, you can perform something like XLOOKUP(1, (Range1=Value1)*(Range2=Value2), Return_range) which will find the row where both conditions are true (because the multiplication of two boolean arrays will yield 1 only where both are true). This is a bit advanced, but it’s more flexible than VLOOKUP, which would typically need a helper column combining the criteria. In summary, XLOOKUP can adapt to multi-criteria lookups more gracefully, especially with Excel 365’s support for dynamic arrays, whereas VLOOKUP struggles with that without extra work.

In all these points, XLOOKUP comes out ahead for modern Excel use. That said, VLOOKUP isn’t completely useless now – it’s still available and works in all Excel versions, and if you have a very simple exact-match lookup in an older file, VLOOKUP can still do the job. But if you have the option, XLOOKUP is generally superior. A quick summary of the above differences:

Summary of XLOOKUP vs VLOOKUP: XLOOKUP is available only in newer Excel versions (Excel 365/2021+), but it offers major improvements: it can look left or right, return results from multiple columns, has a safer exact-match default, built-in not-found handling, ability to search from last or do binary search, no 255-character limit on lookup keys, handles multi-criteria easier, and its formulas are more robust to structural changes. VLOOKUP works in all versions (even older ones) but is limited by all the above constraints. In a nutshell, while VLOOKUP has been a trusty friend for decades, XLOOKUP is the new superstar that “offers a more robust and user-friendly alternative” for lookups. If you have Excel 365 or 2021, you should definitely give XLOOKUP a try – you’ll likely find it simpler and more powerful for your tasks.

(Quick mention: What about HLOOKUP? HLOOKUP is basically a horizontal version of VLOOKUP (searching in the first row instead of first column). XLOOKUP replaces HLOOKUP as well because XLOOKUP can lookup in a row and return from another row just as easily as it does with columns. All the benefits we discussed (like not needing the lookup in the first row, etc.) apply analogously. In short, XLOOKUP can do what HLOOKUP does – and more – so you don’t really need HLOOKUP if XLOOKUP is available.

XLOOKUP vs INDEX/MATCH: Which One Should You Use?

Before XLOOKUP arrived, the reigning “advanced” lookup technique in Excel was the combination of INDEX and MATCH. Many users turned to INDEX/MATCH as a more flexible alternative to VLOOKUP, since with INDEX/MATCH you could overcome some of VLOOKUP’s issues (like left-lookups). If you’re already comfortable with INDEX/MATCH, you might wonder: Can XLOOKUP completely replace INDEX/MATCH? And which is faster or better? Let’s compare them on a few points:

1. Complexity and Ease of Use: The INDEX+MATCH combo involves using two functions together. Typically, you’d write something like =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). This isn’t too hard, but for beginners it’s definitely more to chew on than a single XLOOKUP formula. XLOOKUP consolidates that into one function, which often reads more intuitively: XLOOKUP(lookup_value, lookup_range, return_range). There’s no need to remember to put 0 for exact match (it’s default in XLOOKUP) or to nest functions. So in terms of simplicity, XLOOKUP has the edge with a single, straightforward syntax. It’s easier to write, read, and teach to others compared to INDEX/MATCH, which can appear arcane to the uninitiated. Also, debugging is a bit easier with XLOOKUP since you evaluate one function rather than two nested ones.
2. Compatibility: One clear area where INDEX/MATCH still wins is compatibility. As mentioned, XLOOKUP only works in the newest Excel versions. INDEX and MATCH work in all versions of Excel, even going back decades. If you’re collaborating with someone using an older Excel or you need to maintain a file that must be opened in Excel 2016, you cannot use XLOOKUP there. In those cases, sticking to INDEX/MATCH is necessary. So, while XLOOKUP might be “better”, you always need to consider the environment where your workbook will be used. If backward compatibility is important, INDEX/MATCH remains the reliable choice.
3. Flexibility and Capabilities: Purely in terms of what they can do, XLOOKUP and INDEX/MATCH are quite comparable – both can handle vertical and horizontal lookups, left lookups, exact/approximate matches, wildcards, etc. In fact, anything you could do with INDEX/MATCH, you can also do with XLOOKUP. However, there are some nuances:
   • Approximate Matches: Traditional MATCH (and thus INDEX/MATCH) can do approximate matches only if data is sorted, much like VLOOKUP. XLOOKUP doesn’t have that limitation when using the 2 (wildcard) or default exact match. It only requires sorted data for the binary search modes which are optional. Additionally, Excel 365 introduced XMATCH – a modern replacement for MATCH that, like XLOOKUP, defaults to exact match and can search last-to-first and do binary searches on unsorted data. If you pair INDEX with XMATCH, many differences vanish, because XMATCH basically gives INDEX/MATCH combo the same superpowers of flexible matching and search direction that XLOOKUP has. But again, XMATCH is only in new Excel, so that combination is no more backward-compatible than XLOOKUP itself.
   • Multiple Criteria: Both XLOOKUP and INDEX/MATCH can be extended to handle multiple criteria (though not with a simple built-in parameter; it requires techniques like multiplying logical arrays or using helper columns). The approaches are similar in complexity for both. Neither has a clear built-in multi-criteria argument, but dynamic array formulas and clever tricks can be applied to each.
   • Returning Multiple Columns/Rows: INDEX/MATCH traditionally returns a single value, but you can make INDEX return a whole row or column by using zero as the row or column index. For example, INDEX(C2:E10, MATCH("Alice", A2:A10, 0), 0) could return an entire row of results (if you enter it as a dynamic array formula in Excel 365). Similarly, XLOOKUP can return multiple columns as we discussed. So both can return arrays of results. However, XLOOKUP makes it a bit more straightforward (just select the return range spanning multiple columns). With INDEX, you’d typically use a separate MATCH for row and column in a two-dimensional lookup scenario, or use a trick like 0 index.
   • Non-contiguous Lookups: In some complex scenarios, you might want to lookup from non-contiguous ranges or perform more specialized lookups. INDEX/MATCH being a formula combo allows some creativity (like using INDEX on an entire table and feeding row and column from MATCH results, etc.). XLOOKUP currently requires the lookup and return ranges to be one contiguous range each. This isn’t a limitation in normal usage, but in highly complex models, sometimes INDEX/MATCH (or INDEX/XMATCH) might afford a touch more flexibility by combining multiple functions.
4. Performance: The big question many advanced users ask is speed – which is faster when dealing with large data? The answer can depend on context:
   • For typical exact lookups on moderately sized data, there’s no noticeable difference for the end user – both XLOOKUP and INDEX/MATCH will compute almost instantly.
   • XLOOKUP has an advantage when you can use its binary search mode on large sorted data – that can drastically speed up lookups (but this is an advanced scenario and requires sorted data). In that case, XLOOKUP can be much faster than a normal linear search that MATCH would do.
   • There have been analyses by Excel experts noting that XLOOKUP is generally very efficient, and sometimes slightly faster than INDEX/MATCH for single lookups because it’s optimized in the Excel engine. However, INDEX/MATCH can be faster in certain cases, especially if you’re doing something like many lookups on a really large dataset where setting up a structured reference or using helper columns might help. For example, some power users found that when retrieving many columns of data, a single XLOOKUP that spills an entire row might be more efficient than lots of separate INDEX/MATCH formulas. Conversely, an INDEX/MATCH pulling from an Excel Table might benefit from certain optimizations.
   • In short, for most users, XLOOKUP’s performance is excellent and likely better or on par with INDEX/MATCH. The differences in speed are only a concern with huge datasets and specific scenarios. As one source put it, “XLOOKUP can perform faster than INDEX MATCH in some cases, especially if you use the binary search mode… however, INDEX MATCH can also be optimized in other ways”. So don’t be afraid that XLOOKUP is slower – in typical use it’s plenty fast (and remember, VLOOKUP and HLOOKUP were actually known to be slower and more memory-intensive than INDEX/MATCH, so XLOOKUP was designed to be an improvement there as well).
5. Error Handling: Just like with VLOOKUP, INDEX/MATCH has no built-in error handling. If MATCH can’t find a value, it returns #N/A, which then propagates through INDEX. So you’d again use IFERROR around it. XLOOKUP, as we know, has the nifty [if_not_found] right inside. This is a small convenience but worth noting: XLOOKUP saves you from extra functions to handle not found cases.
6. Familiarity and Existing Use: This is more of a human factor – many advanced Excel users have been using INDEX/MATCH for years and are very comfortable with it. There’s nothing wrong with continuing to use it if it works for you. XLOOKUP is new, and while it’s better in many ways, people have their preferences. Some might still prefer INDEX/MATCH for certain tasks or just out of habit. One nice thing is, if you learn XLOOKUP, you’ve essentially learned INDEX/MATCH too (conceptually). And vice versa: if you already understand INDEX and MATCH, XLOOKUP will feel like a natural, if not overdue, evolution.

Bottom Line: For those with the latest Excel, XLOOKUP can replace INDEX/MATCH in most scenarios. It’s simpler (one function), easier to read, and has a few extra features (like built-in error handling and reverse search) that make it very appealing. However, INDEX/MATCH isn’t “wrong” to use – it’s still extremely powerful and is the only choice in older versions or when ensuring compatibility. You might still encounter many workbooks that use INDEX/MATCH, so it’s good to know both. In many Excel communities, the debate of XLOOKUP vs INDEX/MATCH has settled on: use XLOOKUP if you have it, fall back to INDEX/MATCH if you don’t. Each has strengths, but XLOOKUP was essentially created to be a better INDEX/MATCH (Bill Jelen, a.k.a. Mr. Excel, noted XLOOKUP combined the best of these lookups with added simplicity).

To quote a quick summary: “XLOOKUP is a modern option with a wide range of useful features, making it a good choice for many situations. INDEX/MATCH is an enduring solution that has been used for decades… some users may prefer it for its familiarity or specific needs”. In practice, if you can use XLOOKUP, you probably will for most tasks – but knowing INDEX/MATCH ensures you’re covered for anything and any Excel version.

Practical Examples of XLOOKUP in Action

Let’s solidify our understanding with a few examples that showcase how XLOOKUP can be used in real-world scenarios. We’ll also compare the formulas to their VLOOKUP or INDEX/MATCH counterparts so you can see the differences.

Example 1: Basic XLOOKUP vs VLOOKUP

Scenario: You have a product price list and want to find the price of a specific item. This is a classic lookup task.

Data setup:

Suppose we want to find the price of the product with ID 102 (which is Banana).

• Using VLOOKUP: In the old way, you’d use the ID as the lookup value, and since ID is the leftmost column, it works. The formula would be =VLOOKUP(102, A2:C4, 3, FALSE). This tells Excel to look for “102” in the first column of A2:C4, and return the value from the 3rd column (Price). This would correctly return 0.50. However, what if we wanted to lookup by Product Name instead? With VLOOKUP, we’d have an issue: Product Name is the second column. VLOOKUP can only search in the first column. We’d have to either move the Product Name to be the first column or use a different approach (like INDEX/MATCH) to search by Name.
• Using XLOOKUP: Let’s do the lookup by Product Name to demonstrate XLOOKUP’s flexibility. Say we want the price of “Banana” (product name). We can use:=XLOOKUP("Banana", B2:B4, C2:C4, "Not found") Here, "Banana" is the lookup_value, B2:B4 is the lookup_array (Product Name column), and C2:C4 is the return_array (Price column). We also included an if_not_found argument to return “Not found” if the product name isn’t in the list. This formula will scan B2:B4, find “Banana” in B3, and return the corresponding value from C3, which is 0.50. Notice how straightforward that is – we explicitly told XLOOKUP where to look (names) and where to retrieve from (prices). We didn’t have to include the ID column at all because we weren’t using it in this lookup. If we tried to do VLOOKUP("Banana", A2:C4, 3, FALSE) without rearranging columns, it would fail (it would look for “Banana” in column A and not find it). XLOOKUP had no such issue. Also, if “Banana” wasn’t in the list, our XLOOKUP would neatly return “Not found” instead of #N/A thanks to the if_not_found argument. With VLOOKUP, we would have had to wrap the formula like IFERROR(VLOOKUP(...), "Not found") to get a similar effect.

Takeaway: XLOOKUP can do anything VLOOKUP does in this basic scenario, and more. You can choose your lookup column arbitrarily. This means you don’t have to re-order columns or resort to complex formulas just to accommodate the function’s limitations. For a simple price lookup, both work (if data is aligned for VLOOKUP), but XLOOKUP is more flexible in how you can set up your data. Plus, adding that friendly not-found message was one simple extra argument.

Example 2: Using Excel XLOOKUP function for a Left Lookup (Value to the Left)

Scenario: This is a situation that would trip up VLOOKUP. Let’s extend our product table or modify it. Imagine our table was set up differently:

Now the Product Name is in the first column, Product ID second, Price third. What if we want to find the Product ID of “Cherry”? That is, lookup by product name and return the ID. In this layout, “Cherry” is in the first column and ID is to its right (second column). Actually, VLOOKUP can handle this because it still requires lookup in first column – here, the first column does contain the product names. So we could do =VLOOKUP("Cherry", A2:C4, 2, FALSE) and that would give 103. That works because of how we arranged the table here.

But flip the scenario: what if the table was Name (first), Price (second), ID (third)? And we still want to get ID by Name. In that case, ID is to the right of Name, which is fine for VLOOKUP (just use col_index 3). Let’s make it harder: what if the table was Product ID (first), Price (second), Product Name (third) – so Product Name is last. Now, trying to get ID by name means the lookup column (Name) is the third column, and we want to return the first column. VLOOKUP cannot do that directly because it won’t look in the third column for the name when the table array’s first column is ID.

With XLOOKUP, it doesn’t matter. We simply set lookup_array to the Name column, and return_array to the ID column. For example, with data:

To get the ID for “Cherry” we do:

=XLOOKUP("Cherry", C2:C4, A2:A4)

This looks for “Cherry” in C2:C4 (the Name column) and returns the corresponding value from A2:A4 (the ID column). The result will correctly be 103. VLOOKUP has no single-formula way to do lookup right-to-left like this. The typical workaround was INDEX(A2:A4, MATCH("Cherry", C2:C4, 0)), which of course is perfectly fine – but XLOOKUP achieves it more directly in one function.

This example shows why XLOOKUP is a boon for data that isn’t organized in the one specific way VLOOKUP needs. Real data can have needed info scattered in any column order. XLOOKUP lets you retrieve what you need without restructuring the sheet.

(I recall when I first learned VLOOKUP years ago, I often got stuck because my data wasn’t in the “right” format. I had to either insert helper columns or switch to INDEX/MATCH. With XLOOKUP now, I feel a lot more freedom – I use the data as is, and just tell Excel what to search and what to get.)

Example 3: XLOOKUP vs INDEX/MATCH for Two-Way Lookup (Matrix Lookup)

Scenario: Let’s say you have a matrix of values, for example sales figures by region (columns) and product (rows). And you want to lookup a specific combination, e.g., the sales for “Product B” in the “West” region. This is a two-dimensional lookup (one match on the row, one on the column).

In older Excel:

• You might use INDEX with two MATCH functions: one MATCH to find the row number of “Product B”, another MATCH to find the column number of “West”. Then INDEX (the whole matrix range, row_match_result, col_match_result) to get the intersecting value.
• Or you might use INDEX/MATCH/MATCH in one formula or even consider HLOOKUP inside a VLOOKUP or vice versa (but that gets messy).

With XLOOKUP, there’s a neat way to do this by nesting one XLOOKUP inside another:

1. First, use XLOOKUP to find the entire row for “Product B”. Essentially, lookup “Product B” in the first column of the matrix, and return the entire row of results for that product.
2. That first XLOOKUP will return an array (the row of numbers for Product B across all regions).
3. Then, wrap that inside another XLOOKUP that looks through the header row (regions) to pick the correct column from that returned array.

It sounds complex, but in practice: =XLOOKUP( "West", headers_range, XLOOKUP("Product B", products_range, data_range) ).

The inner XLOOKUP gives the row of data for “Product B”. The outer XLOOKUP looks within that row (by matching “West” in the headers) and returns the corresponding value. This effectively does the same as an INDEX/MATCH/MATCH combination. In fact, Microsoft’s documentation even shows an example of nested XLOOKUP to replace a two-way INDEX/MATCH. XLOOKUP can indeed replace HLOOKUP as well, by using one XLOOKUP for the horizontal part (column match) and one for vertical.

This example is a bit more advanced, but it highlights that XLOOKUP isn’t just for one-dimensional lookups – you can use it creatively for more complex retrievals as well. And it’s arguably more readable than multiple MATCHes and an INDEX. Someone glancing at the formula can parse it: “Okay, they get the row for Product B, then within that they get the West column.” It’s clear if you understand XLOOKUP.

(For the curious, here’s a quick illustration: if products_range is A2:A4 containing [Product A, Product B, Product C], headers_range is B1:E1 containing [North, South, East, West], and data_range is B2:E4 containing the matrix of numbers, then XLOOKUP("Product B", A2:A4, B2:E4) returns an array corresponding to B3:E3, i.e., the row of Product B’s sales in North, South, East, West. Then the outer XLOOKUP finds “West” in B1:E1 and returns the matching element from that array. It’s pretty cool that XLOOKUP handles this seamlessly.)

Example 4: Handling Missing Data Gracefully

Scenario: You have a list of employees and their departments. You want to create a lookup where someone can input an employee ID and get the employee’s name. If the ID doesn’t exist, you’d like to show a friendly message instead of an error.

Data:

Suppose we have this table (ID in first column, Name second, Dept third). We want a lookup formula for name by ID. We also want to handle invalid IDs.

• Using XLOOKUP: Formula could be: =XLOOKUP(input_ID, A2:A4, B2:B4, "Employee not found"). If input_ID is a cell where the user enters an ID (like “E005”), XLOOKUP will search A2:A4. If it finds it, great – returns the name from B2:B4. If not, instead of #N/A, it will return “Employee not found” because we provided that text in the if_not_found arguments.
• Using VLOOKUP/INDEX+MATCH: We’d have to catch the error. For instance, =IFERROR(VLOOKUP(input_ID, A2:C4, 2, FALSE), "Employee not found"). That works, but it’s a bit more to type and remember. INDEX/MATCH would be similar: wrap the whole thing in IFERROR. Not hard, but XLOOKUP makes it a one-step process.

This example is straightforward but shows one of those quality-of-life improvements XLOOKUP brings. When building user-facing spreadsheets (like a small search box for IDs), having the built-in message makes things cleaner.

And by the way, XLOOKUP’s if_not_found isn’t limited to text. You can put 0 or NA() or even another formula (like a secondary lookup) there if you want. It just gives you a lot of flexibility in handling missing data.

Example 5: Finding the Last Occurrence (Advanced)

We touched on this earlier, but let’s see a quick practical use of the search from last feature.

Scenario: You have a transaction log with multiple entries for each customer. You want a formula to get the most recent transaction amount for a given customer.

Data (simplified):

Here, Alice has three transactions ($100, $150, $250 in that order), Bob has two, Charlie has one. If we want the last transaction for Alice, visually we see it’s $250 (the last Alice entry).

• Without XLOOKUP: We’d have to do something like find the last row of the range where Customer = “Alice”. This can be done with a trick using LOOKUP function on a reversed range or using INDEX/MATCH with a reversed search (like MATCH(“Alice”, range, -1) if sorted, but here not sorted by name so that wouldn’t work directly). It gets a bit tricky; one common solution is to combine MATCH with COUNTIF or to sort data descending, etc. No straightforward single formula in older Excel for unsorted data without array formulas.
• With XLOOKUP: We can simply use =XLOOKUP("Alice", A2:A7, B2:B7, , 0, -1). Let’s break that: lookup_value “Alice”, lookup_array A2:A7 (Customer column), return_array B2:B7 (Amount column). We left if_not_found blank (so it would default to #N/A if not found, but we could put something). We set match_mode to 0 for exact match, and crucially search_mode to -1 which means search starting from last item upa. This tells Excel to find “Alice” beginning from the bottom of the list. The first “Alice” it encounters from the bottom is the last occurrence in the normal order. That should return $250. Indeed, XLOOKUP would give $250 for Alice’s last transaction.

For Bob, it would return $180 (last Bob entry), and for Charlie, $300 (only entry). If the customer wasn’t in the list at all, we’d get #N/A (or we could add an if_not_found message as desired).

This example showcases how XLOOKUP handles tasks elegantly that used to require clever approaches. It’s extremely useful in financial or log data analysis where you often want “most recent” records. No more array formulas or manual sorting needed just to get the last match – one XLOOKUP does it.

Conclusion & Next Steps

By now, you should have a solid understanding of the Excel XLOOKUP function and why it’s become a favorite among Excel users. We covered how XLOOKUP works, its syntax, and walked through examples showing its flexibility and power. We also compared XLOOKUP head-to-head with the older stalwarts VLOOKUP and INDEX/MATCH. The verdict? XLOOKUP is a clear winner in terms of features and ease of use for those with modern Excel versions – it provides exact matches by default, can search in any direction, returns multiple columns, includes built-in error handling, and more. It essentially streamlines what used to require multiple functions or complex workarounds in the past.

For Excel beginners, XLOOKUP is a blessing because it simplifies lookups greatly – you can learn one function and do so much with it. For intermediate/advanced users, XLOOKUP is a productivity boost, letting you write cleaner formulas and handle edge cases (like last match, or missing values) with ease. That said, knowing about VLOOKUP and INDEX/MATCH is still useful, especially if working with older files or colleagues who haven’t switched to 365.

As you start using XLOOKUP, here are a few tips to keep in mind:

• Always remember the order of arguments: (lookup_value, lookup_array, return_array, ...). It’s easy at first to mix up lookup_array and return_array. Just think: XLOOKUP = “what, where, return from where”.
• Use the optional arguments to your advantage. Don’t hesitate to add a nice if_not_found message so your spreadsheets are user-friendly. It’s literally what sets apart a beginner-looking sheet from a pro one.
• If you need to do something XLOOKUP doesn’t directly handle (like multi-criteria), consider combining conditions or using helper columns. XLOOKUP can work with the results of those helper setups. In dynamic array Excel, you can often avoid helpers by using expressions inside the function.
• Remember XLOOKUP is not case-sensitive (same as VLOOKUP and MATCH). If you need a case-sensitive lookup, you’d still have to use a workaround (like XLOOKUP on an adjusted range or using EXACT in a logical test).
• Performance-wise, if you are doing thousands of lookups and the file is a bit slow, check if you can use sorted data with binary search (match_mode ±1 and search_mode ±2). It’s an advanced tip, but it can speed things up massively for large datasets. For most typical scenarios, this won’t be necessary though.

Finally, practice is key. Try rewriting some of your old VLOOKUP or INDEX/MATCH formulas using XLOOKUP and see the difference. Personally, once I switched to XLOOKUP in Excel 365, I found myself completing tasks faster and with fewer errors. As I mentioned earlier, it genuinely made my Excel life easier – in fact, I’ve caught myself saying “I wish I had this function years ago!” a few times.

If you enjoyed this tutorial and found it useful, feel free to share it with fellow Excel enthusiasts or colleagues. And if you want to continue improving your Excel skills, we have plenty more resources for you. Excel Pro Tutorial (our site) offers a treasure trove of articles and guides on everything from Excel basics to advanced techniques. We also cover topics beyond Excel – like Power BI for data visualization, SQL for database querying, Python for data automation, and even how to use ChatGPT for boosting productivity.

Thank you for reading this far – that shows your dedication to learning 🎉. Now it’s your turn: go ahead and try XLOOKUP in your Excel sheet. Whether you’re looking up sales figures, inventory items, student grades, or anything else, XLOOKUP can likely make it smoother. Happy Excelling, and remember to check back on Excel Pro Tutorial for more insightful articles to power up your data skills!

— Excel Pro Tutorial Team

If you found this guide helpful, you might also enjoy our other tutorials on Excel and data analysis. Be sure to explore the site for topics like pivot tables, advanced formulas, Power BI dashboards, SQL query tips, and even how to use Python for Excel automation. We’re constantly adding new content to help you level up. Good luck on your Excel journey!