# Excel XLOOKUP Function: Syntax, Examples & Tips - URL: https://betteranalyst.com/excel/xlookup-function - Author: Better Analyst Formula Bot is now Better Analyst—same account, more powerful analysis.Formula Bot is now Better Analyst—same account and workflows, with more powerful analysis, dashboards, and automation. [Learn what changed](https://betteranalyst.com/formula-bot-is-now-better-analyst) The XLOOKUP function retrieves data from a range, allowing for customizable handling of missing values and different search modes. ```excel =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) ``` Generate FormulaExplain Formula One free generation or explanation per browser, shared across Excel and Google Sheets. Account usage limits apply afterward. Describe the XLOOKUP you need in plain EnglishGenerate Formula Need more than a single XLOOKUP formula? Try our free [AI for Excel](https://betteranalyst.com/excel-ai) — it reads your entire spreadsheet, answers questions in plain English, and builds charts and analysis in the same chat. ## Parameters "**`lookup_value`**: The value that you want to search for within the `lookup_array`. **`lookup_array`**: The range of cells that contains the values to search through for the `lookup_value`. **`return_array`**: The range of cells that contains the values to return, corresponding to the found `lookup_value` in the `lookup_array`. **`if_not_found`**: An optional parameter that specifies what to return if the `lookup_value` is not found in the `lookup_array`. **`match_mode`**: An optional parameter that determines how the match is performed: `0` for exact match, `-1` for exact match or next smaller item, `1` for exact match or next larger item, and `2` for a wildcard match. **`search_mode`**: An optional parameter that specifies the search direction: `1` for a search starting at the first item and moving forward, and `-1` for a search starting at the last item and moving backward." ## What is an XLOOKUP? The *XLOOKUP* function in Excel is a powerful tool used to search for a value in a range or array and return a corresponding value from another range or array. It is a more versatile and user-friendly alternative to older lookup functions like VLOOKUP and HLOOKUP. Unlike these older functions, XLOOKUP can search both vertically and horizontally, and it can return multiple results. In simpler terms, XLOOKUP allows you to find specific data in a table and retrieve related information from that table in a straightforward manner. ## A Practical Example Imagine you are managing a product inventory and sales records stored in an Excel spreadsheet: ### Products Table: | ProductID | ProductName | Price | | - | - | - | | 101 | Widget A | 25.00 | | 102 | Widget B | 30.00 | | 103 | Widget C | 15.00 | ### Sales Table: | SaleID | ProductID | Quantity | | - | - | - | | 1 | 101 | 3 | | 2 | 103 | 5 | You want to find the price of each product sold in the sales table. If a product has not been sold, you still want to display its price. ## XLOOKUP Formula To achieve this, you would use the XLOOKUP function as follows: ```excel =XLOOKUP(B2, Products!A:A, Products!C:C, "Not Sold") ``` In this formula: - `B2` is the cell containing the ProductID from the Sales Table. - `Products!A:A` is the range where the function will look for the ProductID in the Products Table. - `Products!C:C` is the range from which the function will return the corresponding Price. - `"Not Sold"` is the value that will be returned if there is no match found. ## Result of the Formula When you apply the XLOOKUP formula to the Sales Table, the output would look like this: | SaleID | ProductID | Quantity | Price | | - | - | - | - | | 1 | 101 | 3 | 25.00 | | 2 | 103 | 5 | 15.00 | If you were to add a row for a product that hasn't been sold, such as Widget B, the result would still show its price as follows: | SaleID | ProductID | Quantity | Price | | - | - | - | - | | 1 | 101 | 3 | 25.00 | | 2 | 103 | 5 | 15.00 | | 3 | 102 | 0 | 30.00 | In this case, if you input ProductID 102 in the Sales Table, the XLOOKUP would return "30.00" for Widget B. ## Why Use XLOOKUP? XLOOKUP is beneficial because it simplifies the process of looking up data in Excel. It eliminates the need for complex nested functions and provides a more intuitive way to retrieve data. Additionally, it allows for error handling by returning a custom message when no match is found, making your spreadsheets more user-friendly. ## Key Takeaways: - **XLOOKUP**: A modern Excel function that searches for a value and returns a corresponding value from another range. - **Versatile**: Can search both vertically and horizontally, making it suitable for various data layouts. - **Error Handling**: Offers the ability to return custom messages for unmatched lookups, enhancing usability. - **Common Use Cases**: Ideal for inventory management, sales tracking, and any scenario where data retrieval from related tables is necessary. XLOOKUP is an essential function for anyone working with data in Excel, providing a powerful way to enhance data analysis and reporting capabilities. Happy Excel-ing! ## Example use case You have a table where Column A contains employee IDs and Column B contains their corresponding names. You can use XLOOKUP to enter an employee ID and quickly retrieve the employee's name. This allows for efficient searching and referencing of employee information without the limitations of older lookup functions. **Beyond the XLOOKUP function:** Better Analyst is a full [Excel AI](https://betteranalyst.com/excel-ai) that goes well past individual formulas. Upload any spreadsheet and ask questions in plain English — you'll get formulas, data analysis, and charts back in seconds. It's a free [AI Excel assistant](https://betteranalyst.com/excel-ai) that works with any .xlsx, .xls, or .csv file — no plugins, no Microsoft 365 required. Don't have a spreadsheet yet? Use our free [AI spreadsheet generator](https://betteranalyst.com/ai-excel-spreadsheet-generator) to turn a text prompt into a downloadable Excel file — a full [AI Excel sheet maker](https://betteranalyst.com/ai-excel-spreadsheet-generator) that builds headers, columns, and formulas from your description. ## Need more help with Excel formulas? Create a free account for formula help, explanations, and spreadsheet analysis. Usage limits depend on your plan. [Create Free Account](https://betteranalyst.com/signup)[Try Formula Generator](https://betteranalyst.com/excel-ai) ## Excel how-to guides - [How To Make A Histogram In Excel](https://betteranalyst.com/excel/how-to-make-a-histogram-in-excel) - [How To Make A Line Graph In Excel](https://betteranalyst.com/excel/how-to-make-a-line-graph-in-excel) - [How To Make A Pie Chart In Excel](https://betteranalyst.com/excel/how-to-make-a-pie-chart-in-excel) - [How To Make A Table In Excel](https://betteranalyst.com/excel/how-to-make-a-table-in-excel) - [How To Merge Cells In Excel](https://betteranalyst.com/excel/how-to-merge-cells-in-excel) - [How To Multiply In Excel](https://betteranalyst.com/excel/how-to-multiply-in-excel) - [How To Password Protect An Excel File](https://betteranalyst.com/excel/how-to-password-protect-an-excel-file) - [How To Remove Blank Rows In Excel](https://betteranalyst.com/excel/how-to-remove-blank-rows-in-excel) ## Explore all Excel formulas Browse our complete library of Excel formula guides Compare your options ## Decide with evidence, not a sales pitch. Open a neutral research prompt in the AI tool you already use. Compare capabilities, limitations, and fit before you choose. 1. [01ChatGPTOpen a balanced comparison prompt in ChatGPT.](https://chatgpt.com/?prompt=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) 2. [02ClaudeOpen the same research prompt in Claude.](https://claude.ai/new?q=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) 3. [03PerplexityResearch the comparison with linked web sources.](https://www.perplexity.ai/search?q=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) AI answers can be incomplete. Check source links, current pricing, and product documentation before deciding.