Xlookup return all matches in one cell. I need to have it return every return value.
Xlookup return all matches in one cell This feature can be used to nest one XLOOKUP inside another to perform a two-way lookup. That means traditional lookup functions like VLOOKUP, XLOOKUP, etc. How It Works: FILTER extracts all rows where a condition is met. 2. What if you want to retrieve all matching rows? Aug 20, 2025 · Discover how to pull data from another sheet in Excel using VLOOKUP, INDEX & MATCH, FILTER, XLOOKUP, and Power Query based on criteria. Download the practice workbook! Sep 9, 2022 · 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! Is this even possible? You will learn all about your options in this article. 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. Aug 16, 2021 · Hi all, I'm doing a spreadsheet where I'm using XLOOKUP to retrieve certain data with multiple criteria. No need to write several statements, as long as the c Oct 7, 2020 · I'm trying to find the ID for each value in a cell. Here you will find 10 XLOOKUP examples + download file Jun 30, 2024 · The INDEX-MATCH and the XLOOKUP formula have one limitation. Mar 20, 2025 · Objective: We aim to find an employee's Department by their Employee ID. FILTER – Return Multiple Matches Unlike XLOOKUP, which returns just one result, FILTER can return all matching values dynamically. 2 days ago · Extracting all matching values for a given lookup criterion and compiling them into one cell enhances data clarity, reporting, and analysis. Perfect for beginners and pros alike, learn syntax, examples, and advanced. Aug 13, 2024 · The best way to use XLOOKUP with multiple criteria is to use Boolean logic to apply conditions. This can come in handy when you are looking to return more than one value! May 26, 2024 · Unlock the power of Excel XLOOKUP Function! Learn syntax, exact & approximate matches, error handling, and advanced techniques. Aug 1, 2024 · 4 easy methods (Formulas and VBA) to lookup and return multiple values concatenated into one cell in Excel. Read more here. 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. Any suggestions are appreciated! Aug 29, 2024 · 1. Select the lookup array. Oct 11, 2024 · To perform a horizontal lookup with the XLOOKUP function, supply a lookup value, a lookup array, and a result array. May 25, 2025 · Discover how to use XLOOKUP to return all matches in Excel. You’ll learn formulas, supporting functions, and Excel tricks that allow you to extract multiple Oct 31, 2023 · XLOOKUP returns only first match? Try these 2 ways to return all XLOOKUP matches for a one lookup value. It can look in any direction, return last match, perform v-lookup or h-lookup with multiple criteria, two-way lookup, and more. We EXCEPT: There are many instances where two or more items in the array have the same value for a given criterion. Working from the inside out, the inner XLOOKUP is used to How to find multiple matching results in Excel (for all excel versions). I'm curious how can I get it to return Dave and Anne 1. Exercise 3: Returning All Matches with the FILTER Function The XLOOKUP function is great, but it stops at the first match it finds. Tip: If working inside a table, use ARRAYTOTEXT to display the results in a single cell. Then using his example put =B20# in the validation list. com/rn5i🆓 DOWNLO. Nov 7, 2022 · This tutorial explains how to use the XLOOKUP function in Excel to return all matches, including an example. If you have Excel 365 or Excel 2021, use XLOOKUP instead of VLOOKUP. The workbook is typically used for a year, so there are a few thousand rows overall. By inputting the name in cell A5, cells B5 to E5 will retrieve the data from the spreadsheet below. In the example shown, the formula in F5 is: Sep 15, 2021 · I would like to know how I can use XLOOKUP to find a match from within a comma separated cell in Column A of Sheet1, and once a match is found, to bring the value from Column B in Sheet2 over to Column B in Sheet1? Please see attached an example of the spreadsheet I am referring to. 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. Formula: =SUM (XLOOKUP (G2, year, data)) Dec 7, 2022 · I have tried XLOOKUP with some success, when only one match is made, but I run into problems when I instead try to use FILTER, I've also tried MATCH. For example, condition "Vegetable" is found in cells B4 Use the XLOOKUP function when you need to find things in a table or a range by row. Select the return array. Aug 14, 2025 · While the concatenation method is a simpler approach to looking up more than one value with XLOOKUP in Excel, the Boolean logic approach is more flexible and can handle more complex scenarios, such as when you use the + symbol to return the first result that matches at least one of the criteria. With a lookup value of "Q3" in cell H4, the result is all values associated with Q3, which spill into the range H5:H16. Lookup and return multiple values concatenated into one cell. I tried using a VLOOKUP but it fails when a cell has more than one value. Arguments Explained lookup_value: The value you want to search for. 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: Apr 29, 2024 · How does your XLOOKUP and INDEX/MATCH functions look? XLOOKUP will only return one value though, INDEX/MATCH can give multiple returns, but it requires a more advanced function ; possibly could you add some mockup data, using Table Markdown if possible. Mar 11, 2023 · So, I was looking for a way to return all the results in drop down list and I can choose the correct one from it. My formula is this For Cell B5 =XLOOKUP(B4,G10:G37,XLOOKUP(A5,A10:A37,H10:H37)) For Cell Apr 17, 2025 · Use VLOOKUP in Excel to return multiple values in a single cell. This article Oct 23, 2023 · The XLOOKUP function contains built-in support for wildcards, but this feature must be enabled explicitly by setting match mode to the number 2. XLOOKUP pulls duplicates of the first return value it finds into all the cells it sees with the same value. The inner XLOOKUP returns a result to the outer XLOOKUP, which returns a final result. May 4, 2023 · XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) As such, you should be able to see that the first 3 parameters of XLOOKUP correspond to the three parameters of LOOKUP, with the primary difference that result_vector / return_array is no longer optional. How to return multiple lookup values in one cell? Here are the steps to get multiple values into a single cell: Use the MLOOKUP function. 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). Boost your productivity with this powerful function. I tried to put the xlookup into a data validation, but it returned the first result only as shown in the below example. Oct 2, 2023 · If we nest this return inside the SUM function, we will return a sum of the values in the cells E8:I8: If we nest this type of return inside of another XLOOKUP function, we can replicate the functionality of the INDEX MATCH MATCH combination. Learn how to use the MLOOKUP function in Excel to lookup and return multiple values in a single cell. Aug 9, 2025 · XLOOKUP: A Modern Take on VLOOKUP and HLOOKUP XLOOKUP is arguably the mother of all lookup functions in Excel, which is surprising given it took Microsoft over 30 years to come up with it! It returns one or more items from a range or array according to the first or last match it finds. The XLOOKUP function returns the values in the result range based on the position where a match was found in the lookup range. Nov 8, 2024 · XLOOKUP can’t return all matches by default; however, here is the workaround with the Excel FILTER function. Jun 27, 2024 · In this article, I explained 4 easy ways to perform XLOOKUP with Multiple Criteria. I need to do an Xlookup based on the CPREF, that returns ALL the Delivery IDs associated with it, ideally by returning the results on the same row. The formula in H5 is: = XLOOKUP (H4,C4:F4,C5:F8) The lookup_value comes from cell H4, which contains "Q3" The lookup_array is the range C4:F4 Use the XLOOKUP function when you need to find things in a table or a range by row. It creates a spill range, displaying multiple matches automatically. Here is my test result: You can change the data range based on your requirement. In this example, it see the first occurrence of serial number 004, but fails to continue on to the second one. And the best part is, this method is quite simple to use, and you don’t need to write a complex formula. Introduced in 2019, XLOOKUP is an upgrade to the VLOOKUP and HLOOKUP functions, providing more flexibility and functionality. Jul 7, 2025 · Learn how to return all matching values in Excel using FILTER, INDEX, array formulas, and more. The XLOOKUP function is easier to use and has some additional advantages. It can also perform approximate matches and wildcard matches, and return an array of results. Apr 2, 2025 · 3. Oct 13, 2022 · XLOOKUP() will return only first occurrence of lookup value. 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. Apr 12, 2024 · This tutorial discusses the purpose and syntax of the XLOOKUP Excel function, its advantages concerning its predecessors, the two main ways of using XLOOKUP with multiple criteria including their pros and cons, extending functionality through the optional parameters, and the way of running a similar search in older Excel versions. May 8, 2025 · Essentially what I (think) would like is a way to combine the functions of Cascading Dropdowns and XLookup to return all names in a single list. Post few sample data as well as show your desired output. At the core, this is a lookup problem, but the twist is that we want to return multiple matches for each team, not just one. Lookup and return multiple values concatenated into one cell [Excel 2019] The image above demonstrates a formula that returns values concatenated based on a condition. 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. May 29, 2020 · Hi William, Indeed, the XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. In the example shown, XLOOKUP is configured to match the value entered in cell E5, which may appear anywhere in the lookup values in B5:B15. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. If we are talking about lookup functions in Excel, everyone knows that XLOOKUP is a Swiss knife in Excel. May 8, 2025 · In this example, the goal is to look up and retrieve all names for a given team and return them in a single cell as a comma‑separated list. com/xloomore 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. Dec 8, 2022 · HiWe have a spreadsheet which records many data on separate tabs. 🚀Get FREE Excel chart templates from Hubspot: https://clickhubspot. Follow this step-by-step guide to consolidate data retrieval for efficient analysis. The formula returns all matching values in a single cell. Read below for details Jun 28, 2019 · MATCH is set up to match 1 as an exact match, and returns the position to INDEX as a row number. Jun 10, 2024 · Here, you will learn how to return array using the XLOOKUP in Excel. Because it works vertically and horizontally, looks up things to the left, right, above, or below, and can Jun 25, 2024 · In other cases, we may want to return the last match or all matches. Step-by-step guide for all Excel versions. Is there a way to force it to pull/lookup the cell one column over from the specific entry in B? Aug 16, 2023 · Whenever I try to perform an XLOOKUP using a range for the "lookup_value" and a "return_array" that has multiple columns, Excel will only ever return the first column. Oct 29, 2024 · Table2[[Item]:[Amount]]: This specifies the range of columns to return data from, starting from the “Item” column to the “Amount” column within Table2. Is there some way to make Xlookup search the entire array for all matches, and return their values? My goal to get the mean, median, and standard deviation of all all data values associated with a particular serial number. 1. The XLOOKUP function below looks up the value 53 (first argument) in the range B3:B9 (second argument). We will delve into the syntax, provide examples, and offer tips to ensure efficient and Feb 21, 2020 · One of the nice benefits of XLOOKUP is it can easily return entire rows or columns as a lookup result. Delivery ID list array is A:F, CPREF is in column E, Deliveries in Column B Feb 2, 2024 · See how to use the XLOOKUP function for vertical and horizontal lookup in Excel. For this example, we’ll be working with two sheets: Sheet1 and Sheet2 Learn how to find the closest match in Excel using the INDEX-MATCH or XLOOKUP formulas (with examples for finding the nearest value). Learn how to lookup multiple criteria in Excel. |Any help of getting all the results in the list? Thanks in advance. The formula in F5 is: = XLOOKUP ("*" & E5 & "*", code, quantity,"no match",2) // returns 50 lookup_value - E5 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. Step 2: Write the XLOOKUP Formula Let's say you want to find the Department of Employee ID 1003. Subscribed 39 22K views 1 year ago #xlookup #datacleaning #exceltricks Excel LOOKUP formula to return multiple values (all matches) https://exceldashboardschool. Includes downloadable file! Feb 13, 2020 · As a quick reminder, unlike VLOOKUP and INDEX / MATCH, the XLOOKUP function is able to return multiple columns. Let’s understand the function first, then use it in our example. XLOOKUP is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. In an empty cell, type: =XLOOKUP (1003, A2:A5, C2:C5) Explanation: 1003: The value to look for. Aug 23, 2025 · Master Excel XLOOKUP function with our comprehensive guide. You will also learn about the FILTER function as a alternative. Steps: Select Cell G5 and insert the following formula. When I filter down to show cells with a 0 value, excel takes a moment to update and will place a price in cells after the filter. Jun 14, 2024 · Learn how to use XLOOKUP in Excel to efficiently search & retrieve data from multiple columns simultaneously. 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. When combined with the FILTER function, XLOOKUP can perform lookups only on filtered subsets of data, making searches more dynamic and efficient. Nov 8, 2024 · The MLOOKUP function is similar to XLOOKUP but can support multiple matches (return values) and display them in a single cell. Enter an equal sign and write the XLOOKUP formula. Sep 27, 2024 · Master Excel formula for partial matches using VLOOKUP, XLOOKUP, Filter and more Examples. 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: Oct 28, 2024 · The XLOOKUP function in Excel is one of the most powerful tools for searching and retrieving data. In the example shown, we want to retrieve all values associated with Q3. Press Enter. Advanced XLOOKUP: The forth argument of XLOOKUP works like the IFNA function. For example (all info is made up), if John worked on September 1st, 3rd, and 4th, I need it to return each date. I need to have it return every return value. While Excel's built-in lookup functions like "VLOOKUP" and "INDEX-MATCH" are great for finding a single match, combining multiple results into one cell requires a more advanced approach. Useful step-by-step example you can use right now. Master XLOOKUP's array formula capabilities to retrieve all matching values, enabling efficient data analysis and lookup tasks with this powerful Excel function. This article explores the different methods and best practices to lookup multiple values and combine them into one cell in Excel, with practical examples, formulas, and explanations. Provide a match_mode argument for an approximate match if needed. Use the XLOOKUP function when you need to find things in a table or a range by row. If you want to return multiple instances match list using formula, we recommend using the INDEX, SMALL and ROW functions. Dec 6, 2022 · To look up and retrieve an entire column, you can use a formula based on the XLOOKUP function. Mar 31, 2024 · This will return the total sum of all matches found within the specified range. The number 1 works for the lookup value because the array now contains only 1's and 0's, as shown above. Steps: Use the following formula in the result cell: Feb 24, 2025 · XLOOKUP is an Excel function used to search for a value in one column and return a corresponding value from another column. Jul 3, 2024 · 7 suitable methods to VLOOKUP and return data based on all matches in Excel. Exact Match By default, the XLOOKUP function in Excel 365/2021 performs an exact match. Oct 3, 2022 · This method has worked for me for around 3 months. In this scenario, B2 would be updated to 4,1 Jun 12, 2023 · This tutorial explains how to use the XLOOKUP function in Excel with an IF statement, including an example. Mar 22, 2023 · Advanced VLOOKUP formula examples show how to Vlookup multiple criteria, return a specific instance or all matches, dynamically pull data from multiple sheets, and more. 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. 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. Add the lookup value. The condition is specified in cell C10, if the condition is met in column B the corresponding value from column C on the same row is extracted and concatenated together. 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. Can we look-up and return multiple values in one cell in Excel (separated by comma or space)? I have been asked this question multiple times by many of my colleagues and readers. Quickly learn how to create an Excel XLOOKUP in Table formula. Make a reference to the lookup value. 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. Note: this problem can also be solved with INDEX With XLOOKUP we can handle lookup values weather horizontally along the rows or vertically along the columns and then SUMIFS will take more than one criteria to sum all the matching values for us! In this lesson, we learn how to return multiple values from a data set with a single Xlookup Statement. Feb 6, 2020 · Learn all about the new Excel XLOOKUP Function. Apr 14, 2025 · The second =XLookup (value, look in column, return array) If your return array has multiple columns in it you can return the entire row but only for the one row. This advanced lookup capability allows users to combine data from different columns into a single cell, making data analysis and reporting more efficient. Unlike VLOOKUP, which has limitations such as requiring a column index and being restricted to left-to-right searches, XLOOKUP can search in both directions (vertical or Feb 21, 2020 · One of XLOOKUP's features is the ability to lookup and return an entire row or column. Note: XLOOKUP performs an exact match by default, so match mode is not set. Use FILTER() instead. 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. If no match is found, it returns the closest match. , won't work because they only return the first match Jan 8, 2025 · XLOOKUP is a powerful Excel function that can return multiple values in one cell. 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. This formula, returns too much information (results highlighted in yellow, below). Step-by-Step Guide: Step 1: Prepare Your Data Make sure your data is in a table format, like the one above. A2:A5: The range with Nov 8, 2024 · Learn how to use XLOOKUP to match text values with the help of the 5th argument, wildcard character match mode. Download our practice workbook, modify data and exercise! 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. 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. Mar 20, 2023 · See how to Vlookup multiple matches in Excel based on one or more conditions and return multiple values in a column, row or single cell. Analyze the best options from the FILTER function to XLOOKUP. Apr 8, 2025 · When working with data in Excel, you may encounter a situation where you need to return multiple matching values for a lookup and display them in a single cell, separated by commas, dash, etc. This article explores an advanced technique with XLOOKUP: how to return all matches when multiple results are found. If more than one value meets the given criteria, they return only the first value. 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 Quickly learn how to create an Excel XLookup return all matches formula. Jan 17, 2021 · In cell F4, you want to return the amount sold depending on the name entered in the cell above, F4. This video answers the following queries: In Excel lookup and return multiple values in one cell. In addition, XLOOKUP also has the ability to handle multiple criteria, allowing for more complex and specific searches. It's way better than VLOOKUP or INDEX/MATCH. This can be done also with INDEX and MATCH, but the syntax is more complex. 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. We want to return the sum, so combine the XLOOKUP function with the SUM function. This makes it a versatile and efficient function for summing all matches in Excel. Note the lookup_value in XLOOKUP is 1 since the logical expressions in lookup_array create an array of 1s and 0s. Apr 13, 2017 · I have a simple question but I'm not finding an answer anywhere. To use XLOOKUP to sum all matches, you need to provide the lookup_value, table_array, column_index_num, and range_lookup as arguments. Oct 22, 2025 · Learn how to look up a value in one column and return a value from another in Excel using VLOOKUP, XLOOKUP, INDEX-MATCH, or LOOKUP functions. Learn both new and classic formulas to get complete lookup results efficiently. The easiest solution if to use the Filter function as suggested by @AhoyNC and output it out of the way somewhere. Learn to utilize XLOOKUP with multiple criteria, filter results, and handle multiple returns. 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. 3. Feb 4, 2024 · This tutorial guides you through harnessing the capabilities of XLOOKUP to fetch results spanning multiple columns and rows in Google Sheets. These ways will help you to use XLOOKUP function. As we stated before, the result is an array containing all values we want to sum. However, in the latest sheet that I have created, the xlookup is not returning prices for cells that have the same criteria as other cells with the same criteria. Sep 10, 2025 · The XLOOKUP function in Excel is one of the most powerful tools for searching data. XLOOKUP Only Returns First Value | XLOOKUP Return all Matches Vertically Or All Matches in One Cell I have a spreadsheet where XLOOKUP will find multiple identical values in its lookup array, but the return array will have different values. The XLOOKUP function in Excel handles vertical or horizontal arrays and supports exact matches, wildcards, and binary searches. 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. 00, the price for a Medium Blue Hoodie. Feb 6, 2025 · Discover how to return multiple matches in Excel using simple methods. Nov 14, 2023 · This tutorial explains how to use the XLOOKUP function in Excel to sum all matches in a range, including an example. qbgflpmezvzbezqpahsfpmgccvtqvshcljsqhwpqdkcnvqepjqewtixjxqavfqgytppyhd