Xlookup return all matches formula. Steps: Select Cell G5 and insert the following formula.

Xlookup return all matches formula Jun 14, 2024 · Learn how to use XLOOKUP in Excel to efficiently search & retrieve data from multiple columns simultaneously. What's more, there are two methods you can use, each differing in complexity and versatility. Step-by-step guide for all Excel versions. All of these work by identifying the first match and return that. It shows the function name, complete syntax format, argument requirements, and return value characteristics. The search area is a range of cells (e. In the example shown, the formula in H8 is: =XLOOKUP(1,(B5:B15=H5)*(C5:C15=H6)*(D5:D15=H7),E5:E15) XLOOKUP returns $29. May 8, 2025 · To look up and return all matches to a single cell, you can use a formula based on the FILTER function and the TEXTJOIN function. These ways will help you to use XLOOKUP function. Enter an equal sign and write the XLOOKUP formula. Sep 10, 2025 · The XLOOKUP function in Excel is one of the most powerful tools for searching data. The XLOOKUP function returns the values in the result range based on the position where a match was found in the lookup range. Quickly learn how to create an Excel XLookup return all matches formula. row or column) you want to find your search value in. I need to have it return every return value. Oct 31, 2023 · XLOOKUP returns only first match? Try these 2 ways to return all XLOOKUP matches for a one lookup value. Now, I use the FILTER function instead. Feb 2, 2024 · See how to use the XLOOKUP function for vertical and horizontal lookup in Excel. To use XLOOKUP to sum all matches, you need to provide the lookup_value, table_array, column_index_num, and range_lookup as arguments. Jun 26, 2024 · XLOOKUP is a powerful function in Excel that allows users to easily find and retrieve data in a spreadsheet. Mar 31, 2024 · XLOOKUP is a powerful function in Excel that allows users to search for specific values within a range and return corresponding values from another range. The first argument is the search or lookup value. In the example shown, the formula in D5, copied down the table, is: =XLOOKUP (B5,quantity,discount,,-1) where quantity (G6:L6) and discount (G7:L7) are named ranges. Oct 11, 2024 · To perform a horizontal lookup with the XLOOKUP function, supply a lookup value, a lookup array, and a result array. Oct 9, 2025 · While XLOOKUP in Excel is primarily designed to return the first match, it can be combined with other functions like FILTER or TEXTJOIN, or used with an array formula to return all matches for a given criterion. In the example shown, the formula in F5 is: {=TEXTJOIN (", ",TRUE,IF (group=E5,name,""))} This is an array formula and must be entered with control + shift + enter. Master this advanced function and unlock a new level of data manipulation. One of its key features is the ability to return all matches for a specific search criterion. Feb 21, 2020 · XLOOKUP can be used to lookup and retrieve rows or columns. Formula: =SUM (XLOOKUP (G2, year, data)) Jun 18, 2025 · Learn how to use XLOOKUP function in Excel using this step-by-step tutorial with 10 practical examples and a free sample workbook. In this comprehensive guide, we’ll explore how to use XLOOKUP to return multiple values in one cell, along with practical examples and tips for optimizing Oct 29, 2024 · Therefore, XLOOKUP will return the matching data from all these columns for the found row. This feature can be used to nest one XLOOKUP inside another to perform a two-way lookup. Note: this problem can also be solved with INDEX Quickly learn how to create an Excel XLOOKUP in Table formula. Working from the inside out, the inner XLOOKUP is used to Apr 2, 2025 · Discover 5 powerful alternatives to XLOOKUP that handle multiple matches, automate lookups, and streamline data analysis in Excel. Provide a match_mode argument for an approximate match if needed. XLOOKUP for BigQuery Aug 14, 2025 · XLOOKUP Formula in Excel with Example =XLOOKUP (lookup_value, lookup_array, return_array, [if _not_found], [match_mode], [search_mode]) XLOOKUP Formula Explanation =XLOOKUP (search for this value, in this range, and Return a match from this range). Because the result is a single text string, it Nov 8, 2024 · Formula: =XLOOKUP (G2, year, data) The formula will search for the lookup value in the header and return all column values in case of a match. If no match is found, it returns the closest match. return_array: The range/column from which May 26, 2024 · XLOOKUP Syntax – Demystifying the Formula Before diving into the magic of XLOOKUP, let’s break down its syntax into easy-to-understand components: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [search_mode], [match_mode]) lookup_value: The value you’re searching for within your data set. It can also be used to sum all matches found within a range, making it a useful tool for data analysis and calculation purposes. Aug 13, 2024 · The best way to use XLOOKUP with multiple criteria is to use Boolean logic to apply conditions. In the example shown, the formula in cell H5 is: =XLOOKUP(H4,quarter,data) where quarter (C4:F4) and data (C5:F16) are named ranges. I have two lists, let's say one is for Deliveries, the other is for Shipping Documents Each Delivery ID has a 'CPREF' (external reference to match shipping doc) Each Shipping Doc has a 'CPREF' (external reference to match to delivery) Relationship: May 21, 2021 · To lookup and retrieve multiple matches in a comma separated list (in a single cell) you can use the IF function with the TEXTJOIN function. With a lookup value of "Q3" in cell H4, the result is all values associated with Q3, which spill into the range H5:H16. By understanding this method, you can efficiently retrieve and analyze data, enhancing your productivity and data management skills. Nov 14, 2023 · This tutorial explains how to use the XLOOKUP function in Excel to sum all matches in a range, including an example. 1. Return area Feb 21, 2020 · One of XLOOKUP's features is the ability to lookup and return an entire row or column. In the example shown, the formula in H5 is: =XLOOKUP (H4,C4:F4,C5:F8) Since all data in the C5:F8 is provided as the return_array XLOOKUP returns the range E5:E8 as a result, which spills into the range H5:H8. While it easily finds a single match, many users often ask: “How can I make XLOOKUP return all matches instead of just the first one?” In this guide, we will walk you through practical methods to achieve this. As we stated before, the result is an array containing all values we want to sum. Xlookup that returns ALL matches Hi I have the following issue I'm trying to reconcile using Xlookup. Result: The formula returns the correct item and amount for the matching invoice and customer. Feb 27, 2023 · The XLOOKUP function has several advantages over its predecessors, including the ability to search for values in any column, not just the first column of a table. We want to return the sum, so combine the XLOOKUP function with the SUM function. The XLOOKUP function in Excel handles vertical or horizontal arrays and supports exact matches, wildcards, and binary searches. Excel has some amazing lookup formulas, such as VLOOKUP, INDEX/MATCH (and now XLOOKUP), but none of these offer a way to return multiple matching values. Nov 8, 2024 · XLOOKUP can’t return all matches by default; however, here is the workaround with the Excel FILTER function. XLOOKUP is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. Download the practice workbook! If you have Excel 365 or Excel 2021, use XLOOKUP instead of VLOOKUP. 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. 00, the price for a Medium Blue Hoodie. It’s the value you look for in your Excel table. Here are the first three arguments again (for more details please refer to this article). Jan 8, 2025 · XLOOKUP is a powerful Excel function that can return multiple values in one cell. Boost your productivity with this powerful function. Jun 27, 2024 · In this article, I explained 4 easy ways to perform XLOOKUP with Multiple Criteria. It extracts every single record meeting Aug 14, 2025 · One misconception about the XLOOKUP function in Microsoft Excel is that you can only input a single condition when searching for a value in your dataset, where, in fact, you can look up values based on multiple criteria. The XLOOKUP function is easier to use and has some additional advantages. Feb 9, 2023 · This property enables the XLOOKUP Function to return more than one column (or row for a horizontal lookup), but the consequence is that we can’t pair a 1D horizontal return array to a 1D vertical lookup array unlike the INDEX-MATCH Formula where we can do opposite orientations: Jun 25, 2024 · In other cases, we may want to return the last match or all matches. Select the lookup array. Support for wildcard characters, enabling more advanced search options. Jan 10, 2024 · The tutorial shows how to use multiple criteria XLOOKUP in Excel, combining AND as well as OR logic, exact and approximate matches, conditions in the same or different columns. In the worksheet shown, the formula in cell F5, copied down, looks like this: =TEXTJOIN (", ",1,FILTER (name,team=E5)) As the formula is copied down, it returns the names for each team in column E, separated by commas. Oct 20, 2024 · Xlookup To Return All Matches The XLOOKUP function in Excel is a powerful tool for data retrieval and has gained immense popularity among users due to its versatility and ease of use. Home statistics Learn How to Return All Matching Values with XLOOKUP and FILTER in Excel Excel data analysis, Excel FILTER function, Excel Formula, Excel lookup formulas, Excel multiple matches, Excel Tips, Excel VLOOKUP Alternative, Excel XLOOKUP, Lookup all matches Excel, Spreadsheet Formulas, Spreadsheet Tips May 6, 2025 · The Excel XLOOKUP function is a powerful tool designed to look up a value in one range and return a corresponding value in another range — it supports approximate and exact matching, wildcards, regular expressions (regex), reverse searches, and lookups in vertical or horizontal ranges. Learn to navigate complex spreadsheets with ease, boosting your productivity and efficiency. Apr 6, 2025 · Handling of exact and approximate matches, allowing for flexible search criteria. Jun 25, 2024 · How to Use XLOOKUP Function in Excel: 11 Practical Examples Example 1 – Use the XLOOKUP Function for Exact Match We will provide the movie name and derive the genre using the XLOOKUP function. com/xloomore I have a spreadsheet where XLOOKUP will find multiple identical values in its lookup array, but the return array will have different values. This article explores an advanced technique with XLOOKUP: how to return all matches when multiple results are found. Simplified formula writing with fewer arguments compared to VLOOKUP. If more than one value meets the given criteria, they return only the first value. This article explores an advanced technique: using XLOOKUP to return all matches within a given dataset. Basic XLOOKUP Example This example demonstrates the simplest use of XLOOKUP to find a product price. For this example, we’ll be working with two sheets: Sheet1 and Sheet2 Apr 4, 2025 · This table breaks down the essential components of the XLOOKUP function. Note the lookup_value in XLOOKUP is 1 since the logical expressions in lookup_array create an array of 1s and 0s. But what if you need a list of all matching records? In Excel versions before 2021, this required complex array formulas. Make a reference to the lookup value. Apr 13, 2017 · If I have a table and I'm trying to find all matches in Column A and return their corresponding Column B, xlookup only returns the first match in this case It would return Dave. It can also perform approximate matches and wildcard matches, and return an array of results. Note that XLOOKUP is not case-sensitive, entering "bcc" in E5 will return the same result: Jun 10, 2024 · Here, you will learn how to return array using the XLOOKUP in Excel. Steps: Select Cell G5 and insert the following formula. Capability to return multiple results, reducing the need for separate formulas. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. Learn to utilize XLOOKUP with multiple criteria, filter results, and handle multiple returns. Syntax: XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) Parameters: lookup_value: The value you want to search for. For example (all info is made up), if John worked on September 1st, 3rd, and 4th, I need it to return each date. So I did a bit of VBA coding to come up with a custom function (also called a User Defined Function) in Excel. This formula uses the named ranges "name" (B5:B11) and "group" (C5:C11). Read below for details Jan 13, 2020 · XLOOKUP Formula To Return All Matches If you are trying to figure out how to get Excel’s XLOOKUP function to return more than one result, you’ve come to the right place! May 23, 2025 · Discover the power of XLOOKUP with our guide, revealing how to effortlessly retrieve all matches in your data. Master XLOOKUP's array formula capabilities to retrieve all matching values, enabling efficient data analysis and lookup tasks with this powerful Excel function. Aug 29, 2024 · 1. com Return All the Matches with FILTER Instead of XLOOKUP In the below example, where I have a value in cell E1, and when I change this value filter returns me all the matches from the main table. lookup_array: The range/column where Excel will search. Use the XLOOKUP function when you need to find things in a table or a range by row. g. It's better than VLOOKUP, HLOOKUP, and some uses of INDEX-MATCH. The XLOOKUP function below looks up the value 53 (first argument) in the range B3:B9 (second argument). Aug 1, 2024 · 4 easy methods (Formulas and VBA) to lookup and return multiple values concatenated into one cell in Excel. It's way better than VLOOKUP or INDEX/MATCH. Subscribed 39 22K views 1 year ago #xlookup #datacleaning #exceltricks Excel LOOKUP formula to return multiple values (all matches) https://exceldashboardschool. Feb 6, 2020 · Learn all about the new Excel XLOOKUP Function. Jul 7, 2025 · Learn how to return all matching values in Excel using FILTER, INDEX, array formulas, and more. Aug 23, 2025 · 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. Mar 14, 2025 · The XLOOKUP function is a powerful tool in Excel, offering a more versatile and user-friendly alternative to its predecessor, the VLOOKUP function. This functionality is particularly useful when dealing with Jan 17, 2021 · Full XLOOKUP syntax Recap first three arguments The XLOOKUP formula can have up to six arguments. You’ll learn formulas, supporting functions, and Excel tricks that allow you to extract multiple Nov 7, 2022 · This tutorial explains how to use the XLOOKUP function in Excel to return all matches, including an example. See full list on thespreadsheetguru. It can look in any direction, return last match, perform v-lookup or h-lookup with multiple criteria, two-way lookup, and more. With XLOOKUP, you can look in one column for a search term, and return a result from the same row in another column, regardless of which side the return column is on. Comparison with VLOOKUP and HLOOKUP VLOOKUP and HLOOKUP have been used for years, but XLOOKUP May 25, 2025 · Discover how to use XLOOKUP to return all matches in Excel. We will delve into the syntax, provide examples, and offer tips to ensure efficient and Nov 16, 2023 · XLOOKUP is a powerful Excel function that allows you to look up a value in a range of cells and return the sum of all matches. Exact Match By default, the XLOOKUP function in Excel 365/2021 performs an exact match. Jun 12, 2023 · This tutorial explains how to use the XLOOKUP function in Excel with an IF statement, including an example. Note: XLOOKUP performs an exact match by default, so match mode is not set. Aug 13, 2025 · 1 FILTER Is the Ultimate Lookup Function It Replaces Multiple Lookups at Once Standard lookups, even XLOOKUP, have a fundamental limitation—they stop and return the very first match they find. Let’s understand the function first, then use it in our example. To get all the values that satisfy the given criteria, you can use the FILTER function of Excel, but this one is also only available in Office 365. Arguments Explained lookup_value: The value you want to search for. Here you will find 10 XLOOKUP examples + download file 2 days ago · Learn how to use regex match in Excel XLOOKUP and XMATCH to search patterns, validate formats, and work with structured data, complete with formula examples and practical tips. Jun 30, 2024 · The INDEX-MATCH and the XLOOKUP formula have one limitation. Read more here. 2. And the best part is, this method is quite simple to use, and you don’t need to write a complex formula. The inner XLOOKUP returns a result to the outer XLOOKUP, which returns a final result. Steps: Use the following formula in the result cell: Dec 6, 2022 · To look up and retrieve an entire column, you can use a formula based on the XLOOKUP function. This advanced lookup capability allows users to combine data from different columns into a single cell, making data analysis and reporting more efficient. This means that users can search for a particular value and have XLOOKUP return all instances of that value within a given range. TEXTJOIN is Oct 23, 2023 · XLOOKUP locates the first match that contains "BCC" (050-BCC-123 in row 10) and returns the corresponding value from the return array, 50. You will also learn about the FILTER function as a alternative. To use XLOOKUP to sum all matches in Excel, you first need to specify the lookup value, the array to search in XLOOKUP Only Returns First Value | XLOOKUP Return all Matches Vertically Or All Matches in One Cell In this Excel tutorial we are learning how to use XLOOKUP with SUMIFS function to look multiple values in the data range and sum multiple matching values bas. If we want to return the last match instead of the first, we can modify our formula to set the Search Mode argument to -1, which searches last to first, as follows: =XLOOKUP(C7,B13:B20,E13:E20,,,-1) The formula returns the last matching value in the range: Mar 20, 2025 · What is XLOOKUP? XLOOKUP helps you find a value in a range or array. Useful step-by-step example you can use right now. 3.