XLOOKUP Function in Excel
- lookup_value: What to look for. The specific text, number, or cell reference you want Excel to search for. Required.
- lookup_array: Where to search. The column or row where Excel should search for your lookup_value. Required.
- return_array: The result to return. The column or row that contains the final answer you want. Make it the same size as lookup_array (the same number of rows). Required.
- if_not_found: Value if not found. What Excel should display if it cannot locate your item (such as typing "Not Found" or 0). If you leave this blank, Excel will automatically show an #N/A error. Optional.
- match_mode: Exact or closest match. Tells Excel whether to find an exact match or the next closest number. You can usually leave this blank because XLOOKUP automatically defaults to an exact match. Optional.
- search_mode: Search direction. Tells Excel whether to search from top to bottom or bottom to top. You can usually leave this blank because it defaults to searching top to bottom. Optional.
What is the XLOOKUP function in Excel?
The XLOOKUP function searches for a specific item in one column or row and returns related information from another column or row.
Think of looking up a friend's name in your phone contacts. You type in their name (the search item), and your phone automatically gives you their phone number (the related information).
Learn with Example: XLOOKUP
- Step 1: Understand the Goal We need to use the Client ID on the first invoice in the Invoice Table to find the matching Client Name in the Client Directory.
- Step 2: Start the Formula Click on the empty cell next to the first invoice. Type: =XLOOKUP(
- Step 3: Select lookup_value For your lookup_value, select the cell containing the Client ID for the first invoice (for example, "CLI-02", it is in cell B11). Then, type a comma. Type: =XLOOKUP(B11, lookup_value Exactly what you want Excel to find.
- Step 4: Select lookup_array For your lookup_array, select the Client ID column in the Client Directory table (A3:A7). Then, type a comma. Type: =XLOOKUP(B11, A3:A7, lookup_array The single column where your lookup_value lives.
- Step 5: Select return_array For your return_array, select the Client Name column in the Client Directory table (B3:B7). Type a closing parenthesis ). Type: =XLOOKUP(B11, A3:A7, B3:B7) return_array The single column that contains the final answer you want.
- Step 6: Check the First Result Press Enter to see the client's name for the first invoice perfectly appear! Result Globex
- Step 7: Lock the Search Areas Before we can copy this formula down to the other invoices, we must lock our search areas so they don't slide down the page. Click on your formula, select A3:A7 and B3:B7, then press F4 on your keyboard to add $ signs. Change This =XLOOKUP(B11, A3:A7, B3:B7) To This =XLOOKUP(B11, $A$3:$A$7, $B$3:$B$7) Hint: Pressing F4 locks the search area so it stays perfectly in place when you drag the formula to the next row!
- Step 8: Copy Down and See the Final Result Now, simply double-click the small green square in the bottom right corner of your cell to copy the formula down. Excel will automatically fill in the names for all the other invoices! Result Globex, Acme Corp, Initech
You have a long list of invoices that only show Client IDs. How do you look up the Client Names from a master directory and fill the entire column with the correct names?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Client Directory | |||
| 2 | Client ID | Client Name | Region | Account Tier |
| 3 | CLI-01 | Acme Corp | East | Gold |
| 4 | CLI-02 | Globex | West | Silver |
| 5 | CLI-03 | Initech | North | Bronze |
| 6 | CLI-04 | Massive Dynamic | South | Platinum |
| 7 | CLI-05 | Soylent | East | Gold |
| 8 | ||||
| 9 | Invoices | |||
| 10 | Invoice # | Client ID | Client Name | |
| 11 | INV-900 | CLI-02 | ||
| 12 | INV-901 | CLI-01 | ||
| 13 | INV-902 | CLI-03 | ||
| 14 | ||||
We need to use the Client ID on the first invoice in the Invoice Table to find the matching Client Name in the Client Directory.
When to use XLOOKUP
Use the XLOOKUP formula when you know one piece of information (like an Employee ID) and need Excel to automatically find related information (like the Employee Name) from another column or table.
- Merge data from two reports: Bring information from one list into another, like matching a list of employee emails to a completely separate list of their sales numbers.
- Automate forms and invoices: Set up your spreadsheet so that simply typing a "Product Code" automatically fills in the correct price and product description.
- Assign categories or owners: Look up a specific region or department in your data and automatically return the name of the manager in charge.
- Update pricing or records: Pull the most current price or status update from a master database directly into your daily working spreadsheet.
When not to use XLOOKUP
XLOOKUP is the best tool for finding related data, but there are three specific times when you should choose a different formula:
- When dealing with duplicate entries: XLOOKUP stops searching as soon as it finds the very first match. If you have five employees named "John" and want to pull the data for all five of them at once, XLOOKUP will only give you the first one. Instead, use the FILTER function.
- When sharing with older Excel versions: XLOOKUP is a modern feature. If you send your spreadsheet to someone using an older version of Excel (like Excel 2016 or 2019), the formula will break and show an error. Instead, use the older VLOOKUP function.
- When searching with multiple rules: If you need to find data based on two or more conditions (for example, finding the price of a "T-Shirt" that is also "Red"), the XLOOKUP formula becomes very complicated to write. Instead, use the FILTER function.
XLOOKUP vs VLOOKUP vs INDEX/MATCH
If you are wondering whether to use XLOOKUP, VLOOKUP, or INDEX/MATCH, use this quick comparison table to see exactly which tool is best for your data.
| Feature | XLOOKUP | VLOOKUP | INDEX / MATCH |
|---|---|---|---|
| Best Used For | Everyday modern tasks | Sharing with older Excel versions | Managing older spreadsheets |
| Search Direction | Any direction | Left to right only | Any direction |
| Can Return Multiple Columns | Yes | No (Only 1 column easily) | No (Only 1 column easily) |
| Exact Match Default | Yes | No (Must type FALSE) | No (Must type 0) |
| Search Bottom to Top | Yes | No | No |
| Excel Version | Microsoft 365 / Excel 2021+ | All Versions | All Versions |
| Ease of Use | Beginner | Intermediate | Advanced |
Bottom Line: Learn XLOOKUP to make your daily work faster, but know the basics of VLOOKUP so you can safely update older Excel files.
Advanced Modes (Optional)
If you need XLOOKUP to perform a complex task, like finding a partial match or searching backwards from the bottom of a list, you will use the final two optional arguments in the formula.
[match_mode] Options
| Number | What it Does | Best Used For |
|---|---|---|
| 0 | Exact match (Default) | Standard searches where you need a perfect match. |
| -1 | Next smaller item | Calculating tax brackets, commission tiers, or grading scales. |
| 1 | Next larger item | Finding minimum order requirements or pricing thresholds. |
| 2 | Wildcard match | Searching for partial text (like typing *Apple* to find "Apple Juice"). |
[search_mode] Options
| Number | What it Does | Best Used For |
|---|---|---|
| 1 | Search top to bottom (Default) | Standard searches to find the very first match on your list. |
| -1 | Search bottom to top | Finding the most recent or newest entry in a growing spreadsheet. |
| 2 | Binary search (Ascending) | Lightning-fast searches on massive datasets (Data must be sorted A to Z). |
| -2 | Binary search (Descending) | Lightning-fast searches on massive datasets (Data must be sorted Z to A). |
Common Mistakes When Using XLOOKUP
Frequently Asked Questions about XLOOKUP
Quick answers about XLOOKUP
Can XLOOKUP look to the left?
Yes! This is the biggest reason people switch from VLOOKUP to XLOOKUP. Because you highlight your search area and your answer area separately, XLOOKUP can easily pull data from any column in your spreadsheet, whether it is to the left or the right of your search area.
Can XLOOKUP search horizontally (like HLOOKUP)?
Yes it can. XLOOKUP completely replaces the older HLOOKUP formula. If you select a single row for your search area instead of a column, XLOOKUP will automatically search horizontally across your screen.
What happens if I have duplicate items in my list?
If you have the exact same name or ID listed twice, XLOOKUP will always stop at the very first one it finds (searching from top to bottom) and give you that answer. If you want to find the last duplicate in the list, you can change the optional search_mode setting to -1 so it searches from the bottom up!
Do I need to sort my data before using XLOOKUP?
No. For standard, everyday searches, your data does not need to be sorted at all. XLOOKUP will easily find your item no matter how messy your list is.
Can XLOOKUP return multiple columns of answers at once?
Yes! If you highlight three columns for your answer area (return_array), XLOOKUP will automatically paste all three columns of data onto your screen at the exact same time. Just make sure you have enough completely blank cells next to your formula so the extra data has room to appear.
Does XLOOKUP work in Google Sheets?
Yes! Google Sheets added the XLOOKUP formula to their software. It works exactly the same way, using the exact same rules and formatting, as it does in Microsoft Excel.
XLOOKUP Practice Workbook
Download ready-to-use Excel files and practice XLOOKUP hands-on
- All 5 worked example tables (finance, sales, HR, ops, data)
- Blank cells to write each XLOOKUP yourself, plus an answer key
- Answer key sheet.