Xlookup return all matches horizontally. Check how to use it with our practical guide.
Xlookup return all matches horizontally In that May 13, 2024 · The XLOOKUP function was released as part of Office 365 to serve as a more modern, convenient replacement to numerous Excel functions, namely the VLOOKUP function. This advanced lookup capability allows users to combine data from different columns into a single cell, making data analysis and reporting more efficient. Jul 3, 2024 · 7 suitable methods to VLOOKUP and return data based on all matches in Excel. Nov 8, 2024 · XLOOKUP can’t return all matches by default; however, here is the workaround with the Excel FILTER function. Apr 4, 2025 · The XLOOKUP function searches a range or array for a match and returns corresponding items. If you want to return multiple instances match list using formula, we recommend using the INDEX, SMALL and ROW functions. Formula examples are available for download. XLOOKUP is fancy but deep down it's like VLOOKUP. This can come in handy when you are looking to return more than one value! This is accomplished when you add more than one column to your Return_array. May 7, 2024 · A2:A50 – the array to return the matching value from XLOOKUP examples with optional arguments Now let’s look at more advanced examples of Excel formulas with this advanced lookup function using optional parameters. The Excel XLOOKUP function is a modern, powerful alternative for older lookup functions such as VLOOKUP, HLOOKUP, and LOOKUP. Example 6: Wildcard Match XLOOKUP can also perform searches using wildcards, allowing you to search for partial matches. XLOOKUP remains the most efficient choice for single-match lookups, effectively replacing complicated nested formulas. To achieve this, you still just need the XLOOKUP function, but be warned – make sure there are enough cells to the right and below as multiple values will populate neighbouring cells. All of these work by identifying the first match and return that. It can look in any direction, return last match, perform v-lookup or h-lookup with multiple criteria, two-way lookup, and more. Jul 5, 2024 · 2 suitable methods to VLOOKUP and return multiple values horizontally in Excel. For example (all info is made up), if John worked on September 1st, 3rd, and 4th, I need it to return each date. Look at the initials of the Lookup functions. For each of those 200 values, you want to display all matches from the lookup table, each in their own column. Download our practice workbook, modify data and exercise! Jul 7, 2025 · Learn how to use Excel’s XLOOKUP function to simplify data retrieval, save time, and master spreadsheets with this quick beginner’s guide. Here is my test result: You can change the data range based on your requirement. Sep 10, 2025 · Learn how to make XLOOKUP return all matches in Excel using FILTER, SEQUENCE, TEXTJOIN, INDEX-SMALL, and Power Query with step-by-step examples. As we stated before, the result is an array containing all values we want to sum. This would result in the 200 values in Column A, with Column B, C, D populated with the matches. It is denoted as a numeral from -1 to 2 which correspond to the following 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. Download our Excel workbook, modify data and exercise! 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. Meaning, you would get only the first match based on the argument you pass on like the Exact Match, Approximate Match, Partial Match, Search Modes, and so on. Jul 29, 2024 · Learn how to master XLOOKUP in Excel with our guide. Ex: =XLOOKUP (A1,C1:C10,B1:B10) If A1 is a match to a value in C1:C10 it will return the value in the B column it matches. We want to return the sum, so combine the XLOOKUP function with the SUM function. It can return a range instead of a single value which spills out the results. Jan 24, 2025 · Learn how to use XLOOKUP in Excel for efficient data lookups. not sure why alot of people are still using vlookup. You'll learn how to copy that filter formula down and display results horizontally. Apr 23, 2024 · XLOOKUP Google Sheets is a major upgrade over VLOOKUP and brings along lots of interesting customization. Use the XLOOKUP function to find things in a table or range by row. Not only that, the Xlookup function can return values even if the dataset is organized horizontally. Feb 2, 2024 · See how to use the XLOOKUP function for vertical and horizontal lookup in Excel. The XLOOKUP function in Excel handles vertical or horizontal arrays and supports exact matches, wildcards, and binary searches. Data Requirement Use xlookup. It's way better than VLOOKUP or INDEX/MATCH. Excel XLOOKUP with match_mode argument The [match_mode] is the fifth parameter in the XLOOKUP formula. Check how to use it with our practical guide. A combination of vlookup and match should do the trick. XLOOKUP can look vertically and horizontally, to the left and right, and supports approximate and exact matching, as well as wildcards (* ?) for partial matches. net Nov 7, 2022 · This tutorial explains how to use the XLOOKUP function in Excel to return all matches, including an example. In the second example you are trying to (vertically) look up a non-unique value in a range and return multiple answers. . For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. I know it is possible by the following lookup formula: But since Excel has XLOOKUP right now and it being capable of whatever lookup ca Feb 21, 2020 · One of XLOOKUP's features is the ability to lookup and return an entire row or column. It offers more flexibility and functionality, supporting approximate and exact matching, wildcards for partial matches, and lookups in both vertical and horizontal ranges. It allows you to find the last occurrence in your data. 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! 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. See full list on exceljet. Feb 6, 2020 · Learn all about the new Excel XLOOKUP Function. Note: XLOOKUP performs an exact match by default, so match mode is not set. 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! 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. May 4, 2023 · How to return corresponding texts from column E to column B. Dec 16, 2024 · You learned about: Key capabilities like vertically/horizontally search keys and return values Functions like VLOOKUP, HLOOKUP and the flexible INDEX/MATCH How to troubleshoot errors When to use new functions like XLOOKUP Real-world examples and practice exercises Lookups do take practice so don‘t worry if not everything sinks in right away. I need to have it return every return value. The inner XLOOKUP returns a result to the outer XLOOKUP, which returns a final result. It can search for values both horizontally and vertically. The number 1 works for the lookup value because the array now contains only 1's and 0's, as shown above. And the best part is, this method is quite simple to use, and you don’t need to write a complex formula. Subscribed 39 22K views 1 year ago #xlookup #datacleaning #exceltricks Excel LOOKUP formula to return multiple values (all matches) https://exceldashboardschool. VLOOKUP’s default setting is an approximate match, which has surprised and confounded countless Excel users, often leading to incorrect results if not specified. With added benefits like default exact matching and the ability to handle errors more gracefully Jan 8, 2025 · XLOOKUP is a powerful Excel function that can return multiple values in one cell. We will delve into the syntax, provide examples, and offer tips to ensure efficient and Sep 4, 2019 · Learn all about the new XLOOKUP function Microsoft has release in 2019. com/xloomore Aug 14, 2025 · The XLOOKUP function in Excel is a powerful and flexible tool designed to replace older lookup functions like VLOOKUP and HLOOKUP. Nov 5, 2025 · Description Use this function to search a range or an array, and return an item corresponding to the first match it finds. =XLOOKUP Use the cell with the value as the first argument, the range you're looking in, then the range you want to return. Because it works vertically and horizontally, looks up things to the left, right, above, or below, and can XLOOKUP Only Returns First Value | XLOOKUP Return all Matches Vertically Or All Matches in One Cell 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 · 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. In the first example you are (horizontally) looking up a unique value in a range and returning the values that lie under that. Unlike these traditional functions, XLOOKUP can search both vertically and horizontally, making it ideal for retrieving specific values from any range in a table. Aug 30, 2021 · Does anyone know there is a cleaner way to write XLOOKUP formula to perform this lookup function. Formula: =SUM (XLOOKUP (G2, year, data)) 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. If vlookup is needed then a good way is to us match to match the header label that matches the header label of the other table that you are calling information from. However, a common mistake Feb 12, 2025 · XLOOKUP references the specific "return" column directly, so your formula updates automatically when the structure of your sheet changes. Oct 31, 2023 · Although Excel’s XLOOKUP function is powerful enough to return sequential rows from the lookup table, it does not return multiple matches for a single lookup value. Edit: the range can be horizontal or vertical. The current formula looks like this, it becomes very long if there are many columns to lookup. How to use XLOOKUP in Excel Step-by-Step Guide In this example below, there are two tables: Jun 28, 2019 · MATCH is set up to match 1 as an exact match, and returns the position to INDEX as a row number. This new Excel function has streamlined inputs that accomplish the capabilities of VLOOKUP, HLOOKUP, and INDEX/MATCH. The XLOOKUP function can retrieve values vertically or horizontally, and even return entire rows or columns, rather than returning a single value. This article explores an advanced technique with XLOOKUP: how to return all matches when multiple results are found. 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 Apr 13, 2017 · I have a simple question but I'm not finding an answer anywhere. Formula: =SUM (XLOOKUP (G2, year, data)) Oct 17, 2024 · Xlookup data vertically & horizontally Hi there, I need to xlookup a rate in a rates table but the ranges for the 2 criteria lookup range are both vertical & horizontal. I'm curious how can I get it to return Dave and Anne I have a spreadsheet where XLOOKUP will find multiple identical values in its lookup array, but the return array will have different values. This feature can be used to nest one XLOOKUP inside another to perform a two-way lookup. Working from the inside out, the inner XLOOKUP is used to Aug 10, 2024 · Excel’s XLOOKUP function is a powerful tool for data retrieval, allowing users to search for a value in a range and return a corresponding value from another range. XLOOKUP can also return more than one value relating to the same match. 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. Jan 5, 2021 · Excel has a great new formula: XLOOKUP! It solves many the problems you've had with VLOOKUP or INDEX/MATCH. May 22, 2022 · Is Xlookup Better than VLOOKUP or HLOOKUP? Yes, Xlookup outperforms Vlookup and Hlookup as the new Xlookup function can perform two-way lookups, find exact, partial, and even wildcard matches. Mar 14, 2023 · The tutorial shows how to do multiple criteria vlookup with INDEX and MATCH in Excel. This is particularly useful when dealing with text data. Oct 17, 2024 · Xlookup data vertically & horizontally Hi there, I need to xlookup a rate in a rates table but the ranges for the 2 criteria lookup range are both vertical & horizontal. Whether you're a beginner or advanced user, discover tips and tricks for efficient data lookup. 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 search vertically or horizontally, making it versatile for various lookup scenarios. 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. Here you will find 10 XLOOKUP examples + download file Learn how to use the XLOOKUP function to transpose data from one column to multiple rows by line item & time period. So I did a bit of VBA coding to come up with a custom function (also called a User Defined Function) in Excel. 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. It can perform a partial match lookup using wildcards. Sep 16, 2025 · XLOOKUP Syntax The XLOOKUP function has the following 5 arguments in its syntax: XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) The first three arguments are required: lookup_value - Value to search for lookup_array - Where to look for the lookup value - range (row or column), or array return_array - Where get return value - range (row or column You'd like to do a VLOOKUP but return ALL match results instead of just the first match? You can use Excel's new FILTER function. It defaults to an exact match. The comparison between XLOOKUP and FILTER is not about determining which function is inherently superior, but rather understanding which function is appropriate for the specific data retrieval task at hand. Syntax XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found] Feb 13, 2020 · Multiple Matching XLOOKUP Refresher As a quick reminder, unlike VLOOKUP and INDEX / MATCH, the XLOOKUP function is able to return multiple columns. Here are the basics! Apr 14, 2025 · The behaviour of the second form, although counter-intuitive, doesn't really surprise me. Jul 7, 2025 · Learn how to return all matching values in Excel using FILTER, INDEX, array formulas, and more. In this video, we will explore how to use the FILTER function in Excel to return multiple values horizontally. XLOOKUP () can find values vertically and horizontally using several criteria, and it can even return an entire column or row of data rather than just one value. Oct 28, 2024 · Formula =XLOOKUP(45000, A2:A5, B2:B5, , -1) Result The formula will return 700, as 40000 is the closest lower salary to 45000, and its bonus is 700. Jan 17, 2025 · Here's how to XLOOKUP multiple criteria using expressions: When you do an XLOOKUP with multiple criteria, it looks for the first instance where all criteria match and returns the corresponding value in the return range. Step-by-step guide for all Excel versions. Nov 28, 2019 · The Excel XLOOKUP function is a new and a more flexible replacement for older functions like VLOOKUP, HLOOKUP, or INDEX and MATCH. Discover tips, tricks, and advanced features to master this powerful function.