Xlookup return multiple values vertically Also, you can tell XLOOKUP which column or row to pull a value from - it's easier than the older functions! Available in MS 365 and Excel 2021 Feb 6, 2020 · XLOOKUP is a function that allows you to quickly look for a value in a dataset (vertical or horizontal) and return the corresponding value in some other row/column. Feb 13, 2020 · Explains how to use the TRANSPOSE function along with the XLOOKUP function in order to return multiple values by row instead of by column. Additionally, you have examples of other types of lookups, where you will learn to use XLOOKUP to match multiple search keys, return multiple values related to your search key, and use it with named Jun 25, 2024 · Example 3 – Return Multiple Values by Applying the XLOOKUP Function in Excel We will find all the listed details for a given movie name. Steps: Select Cell C12 and insert the following formula. One of the primary benefits of XLOOKUP is that it can look up from columns to the left in a data XLOOKUP with multiple criteria in Google Sheets searches a range of cells or a table array for a given value and returns an exact, partial, or approximate match from another column horizontally and vertically. Among its vast array of functions, VLOOKUP (Vertical Lookup) is one of the most popular for retrieving data from large tables based on specific criteria. XLOOKUP offers two primary methods for handling multiple criteria searches: Boolean logic and Concatenation. . Furthermore, it allows users to search for the desired value from the horizontal and vertical data sets. If your version of Excel does not support XLOOKUP, read how to return multiple columns using VLOOKUP instead. Discover tips, tricks, and advanced features to master this powerful function. XLOOKUP is a powerful function in Microsoft Excel, available to Microsoft 365 users, that allows you to search and retrieve data both vertically and horizontally. 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. Sep 10, 2025 · The XLOOKUP function in Excel is one of the most powerful tools for searching data. You will still reference a lookup value, however, instead of specifying an entire data set range, you can simply specify the ID column and the return value column (similar to a SUMIF function). Apr 6, 2025 · Key Takeaways XLOOKUP is the most versatile and powerful lookup function in Excel, allowing for greater flexibility and efficiency in data analysis. It can look in any direction, return last match, perform v-lookup or h-lookup with multiple criteria, two-way lookup, and more. Nevertheless when I provide only one lookup_value it works well. Jan 24, 2025 · Learn how to use XLOOKUP in Excel for efficient data lookups. Jul 24, 2025 · Master Excel XLOOKUP with these expert tips and techniques to transform your Excel skills and tackle complex data challenges effortlessly. Below is an example of how a Double XLOOKUP works to allow for dynamic vertical AND horizontal lookup inputs. Apr 6, 2025 · Key Takeaways XLOOKUP is a powerful Excel function exclusively available for Microsoft 365 users. 2. The inner XLOOKUP returns a result to the outer XLOOKUP, which returns a final result. Select the lookup array. XLOOKUP Return Consecutive Columns One advantage of the XLOOKUP Function is that it can return multiple consecutive columns at once. This advanced lookup capability allows users to combine data from different columns into a single cell, making data analysis and reporting more efficient. 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. Is there a fix to it? Nov 27, 2025 · Lookup and Return Multiple Values Concatenated into One Cell in Excel Excel is an immensely powerful tool used by professionals across numerous fields—from finance and data analysis to project management and beyond. The key to performing a two-way lookup with XLOOKUP is replacing the result range of one XLOOKUP with another XLOOKUP. Sep 16, 2025 · Formulas > Lookup > XLOOKUP Excel XLOOKUP Function Examples You can use the Microsoft Excel XLOOKUP function for vertical search in a column (like VLOOKUP), or horizontal search in a row (like HLOOKUP). Note: XLOOKUP performs an exact match by default, so match mode is not set. While XLOOKUP isn't available in Google Sheets. Feb 6, 2021 · I'm attaching the sample file. The XLOOKUP function syntax includes parameters like lookup_value, lookup_array, return_array, if_not_found, match_mode, and search_mode. 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. 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. The function excels Use the XLOOKUP function to find things in a table or range by row. Aug 10, 2024 · Retrieve Multiple Columns Simultaneously: XLOOKUP can return an array of values from multiple columns by specifying a range in the return_array, saving you from writing multiple lookup functions. Columns in Excel Excel’s XLOOKUP function is a powerful tool that simplifies the process of searching for values within a range or array … Jul 4, 2024 · In this article, I have shown you 3 suitable ways to vlookup multiple criteria in both horizontal and vertical direction. The advantage of XLOOKUP is that it allows you to search for results both to the left and right of your search value in rows and columns. This feature can be used to nest one XLOOKUP inside another to perform a two-way lookup. But what if you need to base your search on multiple criteria? In this article, we will provide a detailed guide on how to use XLOOKUP with multiple criteria, complete with examples, explanations, and practical tips. Feb 21, 2020 · Explanation One of XLOOKUP's features is the ability to lookup and return an entire row or column. 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 Jul 27, 2020 · JonnyGreenwood Sorry but that is the way it is, Excel cannot handle arrays of arrays. What's more, there are two methods you can use, each differing in complexity and versatility. The Excel XLOOKUP function searches for a value, offering flexible matching options and improvements over VLOOKUP and HLOOKUP. Enter an equal sign and write the XLOOKUP formula. Analyze the best options from the FILTER function to XLOOKUP. We search in the cell range B3 to B13 for the ID: =XLOOKUP (I3, B3 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. Feb 28, 2025 · I've been trying to return multiple columns with Xlookup with multiple lookup values, but only the first column is returned. 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. This makes it an invaluable tool Jun 8, 2025 · What is XLOOKUP Function in Excel? XLOOKUP is a versatile lookup function introduced in Excel 365 and Excel 2021 that searches for a value in one array and returns a corresponding value from another array. Aug 23, 2025 · Master Excel XLOOKUP function with our comprehensive guide. Jan 31, 2021 · 2D XLOOKUP step-by-step The approach is to use a XLOOKUP to return a range of cells containing with all the last names. Read more here. Mar 29, 2021 · Is there a way for me to use XLOOKUP to return multiple different return values for identical lookup values? For example, in a table like the one shown below, I would want to return A, B, C, D for lookup value 1. This innovation is particularly powerful when combined with modern functions like XLOOKUP, which can return arrays. Learn how to use Excel XLOOKUP function & return data from multiple columns with one formula. You can, for example, use only [missing_value] and [search_mode] arguments and leave [match_mode] blank. Despite its versatility, certain tasks that seem straightforward—like performing lookups—can become complex, especially when you wish to retrieve multiple matching values and Apr 23, 2024 · In such a case, for the value found in A5, XLOOKUP in Google Sheets will return values from B5, C5, and D5. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. Aug 13, 2024 · The best way to use XLOOKUP with multiple criteria is to use Boolean logic to apply conditions. It has a new set of arguments and is available in Excel 2021 and 365. Nov 7, 2022 · This tutorial explains how to use the XLOOKUP function in Excel to return all matches, including an example. When you need to look up one search key vertically and another horizontally, you can rely on XLOOKUP in Google Sheets. Syntax XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found] It can search for values both horizontally and vertically. , table). It can return a range instead of a single value which spills out the results. 3. Make a reference to the lookup value. This new input format lends much Aug 29, 2024 · How to Use XLOOKUP with Multiple Criteria Rows vs. 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. Oct 11, 2024 · To perform a horizontal lookup with the XLOOKUP function, supply a lookup value, a lookup array, and a result array. Lookup and return multiple values [Excel Defined Table] Return multiple values vertically or horizontally [UDF] VLOOKUP with 2 or more lookup criteria and return multiple matches Extract multiple values based on a search value sorted from A to Z Extract multiple values based on a search value sorted from A to Z (Excel 365) Nov 29, 2024 · The XLOOKUP function in Excel is one of the most versatile tools for data retrieval. You would need to specify Mar 9, 2023 · The XLOOKUP function is a replacement for Excel’s traditional VLOOKUP function (as well as HLOOKUP and INDEX / MATCH functions). From this, we pick the correct one, belonging to ID 10578. #xlookup #excel Nov 8, 2024 · Learn how to sum multiple rows or columns based on a lookup value you can use the SUM function with the XLOOKUP function. 6 days ago · How to Use the Excel VLOOKUP Function to Return Multiple Values Vertically – 5 Methods Microsoft Excel is an immensely powerful tool for data analysis, management, and reporting. 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. However, while VLOOKUP works superbly for retrieving a single Apr 9, 2025 · 2. You will also learn about the FILTER function as a alternative. You’ll learn formulas, supporting functions, and Excel tricks that allow you to extract multiple 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. Pretty cool, huh? Optional arguments for Google Sheets XLOOKUP: Each of the following arguments is optional. Jun 11, 2024 · Explore efficient methods to VLOOKUP and return multiple corresponding values vertically in Excel with these 5 proven techniques. Jun 10, 2024 · Here, you will learn how to return array using the XLOOKUP in Excel. 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. The XLOOKUP Excel function finds specific values in an array range vertically or horizontally, with an approximate, exact, or partial match. I know I can use xlookup vertically or horizontally, but is there anyway to do both at the same time? I currently have this table, which is pulling from a report I paste into excel. Jul 25, 2023 · One important property of the XLOOKUP Function is that it can return a 1D array (vertical or horizontal) if the given return array is in 2D (i. Use the XLOOKUP function to find things in a table or range by row. It outperforms VLOOKUP in terms of flexibility and variety. Oct 28, 2024 · 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 horizontal), return arrays, and handle missing values gracefully. In "Cal" sheet, I want a single cell formula in A2, to lookup values in subsequent columns For some reason Xlookup is spilling lookup vertically in rows. Because the vertical search value is given in cell I3, the XLOOKUP function starts with =XLOOKUP (I3. It offers a more flexible and straightforward approach to searching for data across rows and columns. Sep 20, 2019 · Have you heard of an XLOOKUP/XLOOKUP? No, I didn’t stutter in my writing…. In this guide, you will learn all about XLOOKUP and its function syntax. XLOOKUP is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP can also return more than one value relating to the same match. We search for the name from top to the bottom. Learn how to lookup multiple criteria in Excel. Unlike VLOOKUP, which only searches vertically and requires column index numbers, XLOOKUP can search both horizontally and vertically while using direct array references. It allows you to find the last occurrence in your data. Unlike these traditional functions, XLOOKUP can search both vertically and horizontally, making it ideal for retrieving specific values from any range in a table. Provide a match_mode argument for an approximate match if needed. = XLOOKUP (@spillRange, lookupArray, returnTable) looks up a single value and returns a row from the table (assuming XLOOKUP is working in HLOOKUP mode as in your case). Nov 8, 2024 · Learn how to use XLOOKUP with multiple criteria by concatenating lookup values and lookup arrays inside the formula. How to use XLOOKUP in Excel Step-by-Step Guide In this example below, there are two tables: Stocklist containing the product’s SKU, name, price, and cost. 00, the price for a Medium Blue Hoodie. Feb 4, 2023 · This tutorial will demonstrate how to return multiple columns using XLOOKUP in Excel. XLOOKUP can search for an element in a range row-wise and fetch corresponding values from another column, regardless of which side the return column is on. These ways will help you to use XLOOKUP function. Read below for details 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. = XLOOKUP ( spillRange, lookupArray, returnTable) will lookup multiple values but only from the first column. You can use it to find values based on lookup criteria and handle unmatched values as needed. Aug 14, 2025 · The XLOOKUP function in Excel is a powerful and flexible tool designed to replace older lookup functions like VLOOKUP and HLOOKUP. there is now such a thing as a Double XLOOKUP formula and it acts as an alternative to the ever-popular Index/Match/Match formula. 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. You have step-by-step instructions on how to find an exact match for a single search key, both vertically and horizontally. Syntax and Usage XLOOKUP is a powerful function introduced in Excel to replace the traditional vlookup and HLOOKUP functions. Perfect for beginners and pros alike, learn syntax, examples, and advanced. 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. Sep 4, 2019 · How To Use XLOOKUP From the screenshot example above, you should feel very comfortable with XLOOKUP’s inputs as they are very similar in format to VLOOKUP and HLOOKUP. In this case, If the lookup array is a vertical list, then the XLOOKUP Function will return a row, and if the lookup array is a horizontal list, it will return a column. Unlike its predecessors, XLOOKUP can search in any direction, return multiple values, and doesn't default to an approximate match. XLOOKUP Only Returns First Value | XLOOKUP Return all Matches Vertically Or All Matches in One Cell XLOOKUP Function Definition XLOOKUP looks for a specific value in a range of cells or a table array and returns the value from another column with an exact, partial, or approximate match. It allows you to look up a value from an array in a range or table and return one or more results. Aug 29, 2024 · 1. With added benefits like default exact matching and the ability to handle errors more gracefully 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. Note the lookup_value in XLOOKUP is 1 since the logical expressions in lookup_array create an array of 1s and 0s. Jan 8, 2025 · XLOOKUP is a powerful Excel function that can return multiple values in one cell. The XLOOKUP function can retrieve values vertically or horizontally, and even return entire rows or columns, rather than returning a single value. Search value "Milano" is found in cell E2 and the formula returns value "C" from row three in the same column. Jun 27, 2024 · In this article, I explained 4 easy ways to perform XLOOKUP with Multiple Criteria. It's a replacement for functions like VLOOKUP and HLOOKUP. e. Mar 31, 2025 · Now, with spill ranges, when you write a formula that returns multiple values, Excel automatically spills these results into neighboring cells, creating a spill range. It allows users to search for values in a range or array and return corresponding results. In Google Sheets, XLOOKUP is a more advanced form of VLOOKUP and HLOOKUP. XLOOKUP can retrieve the values from left to the Jan 29, 2025 · This example demonstrates how to lookup a value horizontally using the XLOOKUP function and return the corresponding value from the same column.