Xlookup return multiple columns. I hope I'm making it clear, thanks for helping.
Xlookup return multiple columns Nov 7, 2022 · This tutorial explains how to use the XLOOKUP function in Excel with multiple criteria, including an example. If there's a need to return multiple values then FILTER may be a better option or even an aggregation function such as GROUPBY. 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. However, it occurred to me that it may be helpful to have a separate tab with the ability to return a list of all people that fit multiple criteria; for example, everyone in Football program (Lookup 'Sport' and Filter by 'Football'): Use the XLOOKUP function to find things in a table or range by row. To do this, you need to supply a 2-dimensional array or range. 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. The function excels When using the XLOOKUP function to match data from different locations, the CHOOSECOLS function can be combined to return matching results from different columns in specific order. What if there are multiple matching values? By default XLOOKUP returns the first matching value whenever we have multiple matches. Explanation: the XLOOKUP function looks up the last name and returns the ID. What's more, there are two methods you can use, each differing in complexity and versatility. The values in the lookup table are spread across multiple columns and not all columns have values. At the moment I have to copy/paste the formula every time new rows are inserted, so I thought of using ARRAYFORMULA to skip this step. XLOOKUP is everything VLOOKUP is and much more. The Google Sheets XLOOKUP with Multiple Criteria allows users to search for the desired value from the horizontal and . Learn how to use the XLOOKUP function to find things in a table or range by row, and return multiple columns from the same row. Learn how to replace VLOOKUP and HLOOKUP for flexible and efficient data searches. return_array: The range/column from which This video shows how to use Excel's XLOOKUP function with multiple columns or multiple conditions. 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. 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). Jun 22, 2022 · In this example, we have a table that contains 6 columns of codes, and each row of codes belongs to a group in column B. With Excel’s dynamic arrays feature, all values in the return array will be populated (spilled) into multiple cells. The workbook is typically used for a year, so there are a few thousand rows overall. The problem is Feb 10, 2020 · I'm using XLOOKUP (which is AWESOME, btw) to return multiple columns from a table. 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: Learn how to lookup and return multiple values from multiple columns at once from the data using Excel XLOOKUP function with this easy to understand trick in May 29, 2020 · Example formula would be: XLOOKUP (1,A:A,G:G) - the function is searching for the number 1 in column A then returning a value from column G. 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. One of the primary benefits of XLOOKUP is that it can look up from columns to the left in a data Dec 21, 2023 · In some instances yes the same value it could appear more than once in a different column and row within those columns. XLOOKUP is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. This new input format lends much Jan 5, 2024 · I am trying to use the Xlookup function to return values that match my look up value. 00, the price for a Medium Blue Hoodie. ------------------------ Jan 21, 2025 · Learn how to perform Excel multiple column lookups with ease using XLOOKUP, FILTER, SUMPRODUCT, and Boolean logic techniques. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. 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. Dec 8, 2022 · Using XLOOKUP to return multiple (variable) rows Hi We have a spreadsheet which records many data on separate tabs. See examples, formulas, and tips for XLOOKUP with FILTER, CHOOSE, CHOOSECOLS, and MATCH functions. This advanced lookup capability allows users to combine data from different columns into a single cell, making data analysis and reporting more efficient. 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. lookup_array: The range/column where Excel will search. Do you happen to have a sample workbook to share? Nov 24, 2018 · Using OFFSET with XLOOKUP and XMATCH, you can easily perform a two-way lookup and return multiple columns in Google Sheets. Feb 2, 2024 · See how to use the XLOOKUP function for vertical and horizontal lookup in Excel. Aug 25, 2022 · The XLOOKUP function can return multiple results for a single match, not just a single result like a VLOOKUP (although there is a workaround for VLOOKUP to return multiple columns). JoAnne asks XLOOKUP can return multiple columns. For example, you can search for a fruit name in the first column of a range and a month name across the first row, then return the quantity of that fruit for the given month May 25, 2021 · In my recent blog on the XLOOKUP function I showed how useful it was rather than using the VLOOKUP function. Step-by-step guide for all Excel versions. The goal is to lookup any code in C5:H15, and return the name of the group the code belongs to. Nov 21, 2021 · KanwalNo1 If you are trying to return a two dimensional array of values, you can use INDEX/XMATCH/XMATCH or, if you have no need to look up the column indices, a hard-wired array {1,2} will return the first two columns. Table of Contents (0:00) XLOOKUP return Nov 29, 2024 · The XLOOKUP function in Excel is one of the most versatile tools for data retrieval. Jun 14, 2024 · How do I use XLOOKUP to search multiple columns at once? To use XLOOKUP to search multiple columns at once, you need to specify the lookup_value, lookup_array, and return_array arguments in the function. My example image is attached. The formula in K6 is: = XLOOKUP (1, MMULT (-- (codes = K5 Feb 21, 2020 · Explanation One of XLOOKUP's features is the ability to lookup and return an entire row or column. Feb 3, 2021 · Hi I am trying to make xlookup returning mutiple values. However, you can get around this by using Boolean logic or concatenation to instruct XLOOKUP to use multiple search criteria. Dec 4, 2021 · I tried in N3 the formula =XLOOKUP(H3:H4,C3:C6,D3:E6,,0), it returned only the first corresponding column rather than 2 columns. It allows you to look up a value from an array in a range or table and return one or more results. #xlookup #vlookup Apr 23, 2024 · What’s more, XLOOKUP can return results from not just one but from multiple columns out-of-the-box. My lookup value is "cat". May 18, 2022 · Another question from the UCF Accounting Conference. Multiple Values The XLOOKUP function in Excel 365/2021 can return multiple values. I would like to return the lookup values and to exclude any blank values. It's better than VLOOKUP, HLOOKUP, and some uses of INDEX-MATCH. Oct 18, 2021 · What this means is that you can use one function to return multiple columns (or rows) of resulting values. Efficient data retrieval is crucial for productivity in data analysis. Concatenating lookup values and arrays using the ampersand (&) sign can streamline the process of searching for multiple criteria at once in Excel. Currently, Xlookup brings back all of the values which includes the blanks and returns May 12, 2025 · Are you tired of manually searching for data across multiple Excel sheets? Can you imagine a simpler way to retrieve the information you need without the hassle? XLOOKUP is a versatile function designed to simplify this process. For example, imagine your table has three columns: Product ID, Product Name, and Price. This seems like something that *has* to be possible, but I can't figure it out. Aug 2, 2025 · Use XLOOKUP in Google Sheets to search across multiple columns and return results from a matching row—supports top-down or bottom-up search, no LAMBDA needed. This powerful function is a must-have for any Google Sheets user. Nov 14, 2023 · This tutorial explains how to use the XLOOKUP function in Excel to sum all matches in a range, including an example. There's 116 columns and 726 rows. #xlookup #excel 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. When combined with the FILTER function, XLOOKUP can perform lookups only on filtered subsets of data, making searches more dynamic and efficient. Feb 18, 2025 · XLOOKUP (1, BYROW (…), C5:C13): Now that we know which team has the lookup value (Leonard), we just lookup for 1 (count) in the BYROW output and return the corresponding team name from the column C. Dec 2, 2024 · XLOOKUP may not be the best fit for the situation. Feb 20, 2025 · A standard XLOOKUP can't handle this directly because it doesn't know how to check both the Name column and the Department column at the same time. Learn how to use XLOOKUP to find multiple criteria in Google Sheets with this step-by-step guide. The video below walks through how this can be done using the VLOOKUP 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. For example, if you’ve got the scores for students in an exam, you can use XLOOKUP to quickly check how much a student has scored using the name of the student. Keep reading to learn more. After watching Learn how to XLOOKUP & return multiple columns in Excel & Google Sheets. Feb 28, 2025 · I've been trying to return multiple columns with Xlookup with multiple lookup values, but only the first column is returned. Feb 21, 2020 · XLOOKUP can be used to lookup and retrieve rows or columns. We extract some of these data onto a daily printout of salient information. May 8, 2025 · This is easy enough and done mostly with =XLOOKUP to return the data - No Problem. My question is, how do I tell it to keep searching and return the values in column G for all instances of '1' in column A, rather than stopping the first time it encounters '1' in column A? Oct 25, 2024 · Master the XLOOKUP function in Excel with our concise guide. What Is XLOOKUP With Multiple Criteria In Google Sheets? XLOOKUP with Multiple Criteria in Google Sheets looks for a specific value in a dataset and returns the value from another column with an exact, partial, or approximate match fulfilling multiple conditions or criteria. See examples, syntax, arguments, and tips for using XLOOKUP in Excel. The function returns the entire row or column based on the search direction. Oct 25, 2024 · Support for exact and approximate matches Capacity to return multiple values with a single formula Getting Started with XLOOKUP To use XLOOKUP effectively, you need to understand its basic structure. Specifically: and turn on all notifications 🔔 for MS Excel time hacks that will make you more Jul 7, 2025 · Learn how to return all matching values in Excel using FILTER, INDEX, array formulas, and more. Jul 25, 2023 · XLOOKUP in Return Array Similarly, we can use a nested XLOOKUP to return a dynamic return_array. Nov 7, 2022 · This tutorial explains how to use the XLOOKUP function in Excel to return all matches, including an example. It can look in any direction, return last match, perform v-lookup or h-lookup with multiple criteria, two-way lookup, and more. One common Dec 17, 2022 · To answer the question, to do the same with xlookup(), you can combine columns into a compound key, as attempted by the formula in the question. If your version of Excel does not support XLOOKUP, read how to use the VLOOKUP instead. Dec 18, 2023 · I'm using XLOOKUP to return a row with 4 columns. The secret is to combine your multiple criteria into a single, unique string that XLOOKUP can search for. Both are pretty straightforward and easy to use. XLOOKUP Return Consecutive Columns One advantage of the XLOOKUP Function is that it can return multiple consecutive columns at once. Learn how to use Excel XLOOKUP function & return data from multiple columns with one formula. It is a nuisance that, though arrays of arrays, are normal as input (essentially that is what Tables are) it is such hard work to get a 2D array returned. Mar 9, 2023 · The XLOOKUP function is a replacement for Excel’s traditional VLOOKUP function (as well as HLOOKUP and INDEX / MATCH functions). It has a new set of arguments and is available in Excel 2021 and 365. You will also learn about the FILTER function as a alternative. 1. Does anyone know how to write only one array formula covering several rows to return all the corresponding columns? Nov 17, 2025 · 📽️ In this Microsoft Excel video tutorial I explain how to return multiple columns using VLOOKUP and XLOOKUP. 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. g. XLOOKUP can work with arrays natively, opening up the doors to more advanced calculations Jan 31, 2021 · The basic idea is to use one XLOOKUP to return a range of cells (for example a column or row). The challenge is that the code may be in any one of the six columns, and potentially more columns in larger sets of data. For example (all info is made up), if John worked on September 1st, 3rd, and 4th, I need it to return each date. The FILTER function allows you to condense a range of data based on criteria. I'm trying to use XLOOKUP with multiple columns as the lookup array, then return a value from a single column as my return array. If your version of Excel does not support XLOOKUP, read how to use VLOOKUP instead. Perfect for Microsoft 365 users! Oct 11, 2024 · how to return multiple columns of data using the xlookup formula. Note: XLOOKUP performs an exact match by default, so match mode is not set. Columns in Excel Excel’s XLOOKUP function is a powerful tool that simplifies the process of searching for values within a range or array … Mar 20, 2025 · What is XLOOKUP? XLOOKUP helps you find a value in a range or array. Unlike VLOOKUP, which only searches vertically and requires column index numbers, XLOOKUP can search both horizontally and vertically while using direct array references. To combine columns of multiple rows with the & operator, you need to wrap the expression in arrayformula(), like this: =xlookup( C3 & "→" & C4, Oct 9, 2025 · Utilizing XLOOKUP with multiple criteria in Excel allows for more flexible data searching compared to traditional lookup functions. Quickly learn how to create an Excel XLOOKUP in Table formula. 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. With XLOOKUP, you can easily search and fetch […] The XLOOKUP function in Excel handles vertical or horizontal arrays and supports exact matches, wildcards, and binary searches. I prefer the boolean array Apr 6, 2025 · XLOOKUP offers two primary methods for handling multiple criteria searches: Boolean logic and Concatenation. Useful step-by-step example you can use right now. 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. This capability enhances data analysis by enabling searches based on several conditions. XLOOKUP is an Excel function that allows you to search for a value in a range or table and return a value from another column. For example, take a look at the XLOOKUP function below. Oct 29, 2024 · Two effective ways to use XLOOKUP with multiple columns to return the first matching value, and how to use FILTER to retrieve all matching values. So I enter name and want xlookup to return phone number and salary. What if you want to return several non-adjacent columns, for example, January, April, July, October? There are three solutions presented here. =XLOOKUP(G3,B3:B5,XLOOKUP(H3,C2:E2,C3:E5)) Let’s breakdown and visualize the formula: We use the column headers as the lookup array for the first XLOOKUP: =XLOOKUP(G3,C2:E2,C3:E5) Just like in the previous section, the XLOOKUP will return a row once it finds a match from the column headers. Jun 13, 2020 · I'm hoping for some help with the XLOOKUP function, or a recommendation of how else I can achieve my end goal. It gave me a sense of power and cemented my love of Excel. Unlike VLOOKUP, which can only return one colum Aug 13, 2024 · The best way to use XLOOKUP with multiple criteria is to use Boolean logic to apply conditions. Jun 10, 2024 · Here, you will learn how to return array using the XLOOKUP in Excel. 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. 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. Read below for details Jan 8, 2025 · XLOOKUP is a powerful Excel function that can return multiple values in one cell. The XLOOKUP function greatly simplifies the process of returning multiple contiguous columns compared to the VLOOKUP function. I'm hoping for some help with the XLOOKUP function, or a recommendation of how else I can achieve my end goal. The columns can be returned in the same order as your data set, or in a specified Jun 22, 2023 · In this blog post, we'll explore two simple methods to learn how to XLOOKUP multiple criteria in Excel and how it can streamline your data analysis process. Note the lookup_value in XLOOKUP is 1 since the logical expressions in lookup_array create an array of 1s and 0s. Any tips? Solutions? Thanks! 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. This innovation is particularly powerful when combined with modern functions like XLOOKUP, which can return arrays. I need to have it return every return value. What I'd like to do is return Sales Amount and Commission Amount, which are non-contiguous columns in the Table. The XLOOKUP function can retrieve values vertically or horizontally, and even return entire rows or columns, rather than returning a single value. The inner XLOOKUP returns a result to the outer XLOOKUP, which returns a final result. I have a spreadsheet where XLOOKUP will find multiple identical values in its lookup array, but the return array will have different values. In the following table, the XLOOKUP function does these steps:: Multiple Criteria with XLOOKUP (Helper Column Method) This is a simple way to create a new helper column, combining values from two columns and then creating a lookup value to get the score. Implement accurate lookups with Excel XLOOKUP and XMATCH. The Sep 9, 2022 · Using FILTER Instead of XLOOKUP For Multiple Results Since the XLOOKUP function can only return a single row or column’s worth of data when it finds a match, we will need to utilize a different function to carry out our desired result. XLOOKUP can be used to find exact matches, the closest match, XLOOKUP with multiple criteria, and more. A second XLOOKUP wrapped around then returns a single item from this array. Left Lookup Instead of using INDEX and MATCH in Excel to perform a left lookup, simply use the XLOOKUP function. To do this, input the range of consecutive columns in the return array, and it will return all the columns. 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. 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. Jun 29, 2021 · I want to do an xlookup where there are multiple matches and get excel to return the maximum value: e. XLOOKUP can look up to the left XLOOKUP won’t break if columns are inserted or deleted in the lookup array XLOOKUP can find the last occurrence of a value XLOOKUP defaults to an exact match, so new users won’t accidentally return Use the XLOOKUP function to find things in a table or range by row. 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. XLOOKUP () returns data in a table or range by row. Jan 7, 2021 · Return array The XLOOKUP function can also return multiple values as an array. Is there anyway to retun multiple non adjacent values? Thank you very much. Feb 7, 2023 · Thank you! 👍👍 In this Microsoft Excel video tutorial I demonstrate how to include multiple columns and rows in your return array. My lookup range is column A and I want excel to return the max value from column b for "cat": Column A Column B Cat 3 Dog 1 Cat 12 Cat 4 Feb 9, 2023 · This tutorial will demonstrate how to combine the XLOOKUP and IF functions in Excel. Ideally I want to get the return values from column A in worksheet 2 that correspond to the rows my lookup values appear in. Jun 27, 2024 · In this article, I explained 4 easy ways to perform XLOOKUP with Multiple Criteria. Which method do you prefer? 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. Jan 14, 2025 · XLOOKUP is a handy Excel function, but what if you want to find some data in your spreadsheet that requires multiple search criteria to find? When you look at the XLOOKUP formula, it appears that you can only add one lookup value. Aug 16, 2025 · Learn how to VLOOKUP from multiple columns in Excel with one return using a helper column, XLOOKUP, or the INDEX-MATCH formula. But it is not working. These ways will help you to use XLOOKUP function. When training XLOOKUP recently, I was asked if it could return data on columns that were… 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. XLOOKUP Multiple Criteria Concatenation One common way of performing a multiple criteria XLOOKUP is by concatenating all the criteria into one lookup value and their corresponding lookup columns into one lookup array Aug 23, 2025 · XLOOKUP (Excel XLOOKUP Function) can return an array of results if your return_array spans multiple columns. Feb 4, 2023 · Learn how to use XLOOKUP to return multiple columns in Excel, including consecutive, non-adjacent, and different order columns. In this video, I explain how to use the XLOOKUP () function to extract multiple column values at once in Excel. With XLOOKUP, you can quickly and easily find data in a table or range, even if the data is sorted or filtered. XLOOKUP Multiple Lookup Criteria There are a lot of ways to use the IF Function alongside the XLOOKUP Function, but first, let’s look at an example using the core element of the IF Function, the logical criteria. This feature can be used to nest one XLOOKUP inside another to perform a two-way lookup. 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. Boolean logic allows you to use true or false conditions to search multiple criteria simultaneously. Nevertheless when I provide only one lookup_value it works well. Sep 16, 2025 · In this example, we'll use XLOOKUP to return the values from 3 columns in a lookup table, for a specific product code. 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 Jan 10, 2024 · XLookup is capable of doing look-ups with multiple criteria using two methods, concatenation and another boolean array. (See file for example). Feb 8, 2023 · This tutorial will demonstrate how to perform an XLOOKUP with multiple criteria in Excel. In the XLOOKUP return multiple values (columns) example formula, I specify the columns with the multiple values to return: With the third argument (return_array) of the XLOOKUP function. Includes practical examples, including two-way lookups, default values (IFNA replacement), wildcards, Nth/last find, multiple criteria, multiple column returns (spill), and sort/search modes. Aug 29, 2024 · How to Use XLOOKUP with Multiple Criteria Rows vs. It allows users to search for values in a range or array and return corresponding results. Feb 4, 2024 · This tutorial guides you through harnessing the capabilities of XLOOKUP to fetch results spanning multiple columns and rows in Google Sheets. I hope I'm making it clear, thanks for helping. Here, “multiple columns” refers to all columns from the intersection point across the range. wtlppdpgzuwulaasshqilzssqhbeukyfxanfkanbuygufwsvimmimjnoojlbsvygrluepdxifotrjp