Xlookup return multiple matches. Note: you can use the same approach .

Xlookup return multiple matches It can look in any direction, return last match, perform v-lookup or h-lookup with multiple criteria, two-way lookup, and more. TEXTJOIN is Jul 7, 2025 · Learn how to return all matching values in Excel using FILTER, INDEX, array formulas, and more. Table of Contents Example Syntax Arguments Video How to return mul May 15, 2024 · To lookup data based on multiple complex criteria, you can use the XLOOKUP function with multiple expressions based on Boolean logic. You can see this option used in the example shown. The formula in G5, copied down, is: = XLOOKUP May 8, 2025 · This is easy enough and done mostly with =XLOOKUP to return the data - No Problem. Aug 29, 2024 · 1. Make a reference to the lookup value. That is better done with FILTER. This formula uses the named ranges "name" (B5:B11) and "group" (C5:C11). In this lesson, we learn how to return multiple values from a data set with a single Xlookup Statement. Jun 7, 2025 · In its default behavior, if multiple instances of the lookup_value appear in the lookup_array, XLOOKUP () will always return the first match it finds. Feb 21, 2020 · One of XLOOKUP's features is the ability to lookup and return an entire row or column. 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. Are there alternative functions to XLOOKUP for handling multiple criteria? Feb 28, 2025 · I've been trying to return multiple columns with Xlookup with multiple lookup values, but only the first column is returned. 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. Note: you can use the same approach Nov 29, 2024 · The XLOOKUP function in Excel is one of the most versatile tools for data retrieval. Jan 10, 2024 · 1 XLookup is capable of doing look-ups with multiple criteria using two methods, concatenation and another boolean array. 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. Feb 2, 2024 · See how to use the XLOOKUP function for vertical and horizontal lookup in Excel. 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. If you want to return multiple instances match list using formula, we recommend using the INDEX, SMALL and ROW functions. 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. Dec 8, 2022 · Using XLOOKUP to return multiple (variable) rows Hi We have a spreadsheet which records many data on separate tabs. 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. com/xloomore Oct 8, 2021 · X Lookup returning multiple matches Hi. Setting search mode to -1 causes XLOOKUP to search "last to first" or, in other words, search backwards through the data. Check how to use it with our practical guide. 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. It allows you to Nov 8, 2024 · Learn how to use XLOOKUP with multiple criteria by concatenating lookup values and lookup arrays inside the formula. By understanding this method, you can efficiently retrieve and analyze data, enhancing your productivity and data management skills. 1st, 2nd, 3rd, etc. You will also learn about the FILTER function as a alternative. 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. Make sure you check the size and alignment of the ranges you use in the formula so it will return the correct answer. Both are pretty straightforward and easy to use. 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. Jun 27, 2024 · In this article, I explained 4 easy ways to perform XLOOKUP with Multiple Criteria. 2. Apr 6, 2025 · Key Takeaways XLOOKUP is a powerful Excel function exclusively available for Microsoft 365 users. This feature can be used to nest one XLOOKUP inside another to perform a two-way lookup. Keep reading to learn more. The workbook is typically used for a year, so there are a few thousand rows overall. And the best part is, this method is quite simple to use, and you don’t need to write a complex formula. XLOOKUP is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. For example, take a look at the XLOOKUP function below. Oct 1, 2024 · Re: Xlookup & return highest value match Someone seems to have deleted two of the three new threads that AJ started - presumably that Mod/Admin has advised AJ as to why (wasn't me). The inner XLOOKUP returns a result to the outer XLOOKUP, which returns a final result. 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. In the example shown, the formula in G8 is: =XLOOKUP (G7,IF (data [Service]=G6,data [Weight]),data [Cost],,-1) where data is an Excel Table in the range B5:D16. 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. The first xlookup I use a single criteria to lookup my prices, at the end of this xlookup I have nested another xlookup should the first have no value found. However, XLOOKUP offers an optional argument called search_mode to control the order in which data is searched. Sep 10, 2025 · The XLOOKUP function in Excel is one of the most powerful tools for searching data. e. Feb 8, 2023 · 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. 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. It is good to know that it works like other lookups; it returns the first matching record corresponding to a lookup value. To use XLOOKUP to sum all matches, you need to provide the lookup_value, table_array, column_index_num, and range_lookup as arguments. My lookup value is "cat". For example, condition "Vegetable" is found in cells B4 . 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'): 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. 3. The function will search from the top of the lookup_array downwards. 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. The XLOOKUP function syntax includes parameters like lookup_value, lookup_array, return_array, if_not_found, match_mode, and search_mode. It allows users to search for values in a range or array and return corresponding results. If so, then the XLOOKUP function is here to make your life easier! 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. Feb 7, 2025 · The best way to search for multiple criteria in Excel is with the XLOOKUP function, and this guide will show you how to properly use it. Read below for details Jan 8, 2025 · XLOOKUP is a powerful Excel function that can return multiple values in one cell. Explanation: the XLOOKUP function looks up the last name and returns the ID. 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. Oct 13, 2022 · I need to look up values and separate the multiple matches with TEXTJOIN. So I did a bit of VBA coding to come up with a custom function (also called a User Defined Function) in Excel. May 21, 2020 · By default, XLOOKUP will return the first match in a data set. 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. Oct 3, 2022 · I am using an xlookup nested within another xlookup to find pricing for tickets based off of multiple criteria. Select the lookup array. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. Dec 2, 2024 · What you cannot do with any straightforward implementation of an Excel formula using XLOOKUP is to return multiple matches for a given lookup value. In many aspects, the XLOOKUP function surpasses its predecessors in Excel (VLOOKUP, HLOOKUP, and LOOKUP). Step-by-step guide for all Excel versions. For example (all info is made up), if John worked on September 1st, 3rd, and 4th, I need it to return each date. 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. XLOOKUP can be used to find exact matches, the closest match, XLOOKUP with multiple criteria, and more. 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. See full list on thespreadsheetguru. but when there are more than 2 with the same amount, it puts that person twice. No need to write several statements, as long as the c Jun 12, 2023 · In this post, you will see how you can use VLOOKUP, XLOOKUP and FILTER with multiple criteria and return multiple results when you look up values in Excel. I need to have it return every return value. The easiest solution if to use the Filter function as suggested by @AhoyNC and output it out of the way somewhere. 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. 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. com Nov 8, 2024 · XLOOKUP can’t return all matches by default; however, here is the workaround with the Excel FILTER function. This advanced lookup capability allows users to combine data from different columns into a single cell, making data analysis and reporting more efficient. Working from the inside out, the inner XLOOKUP is used to Use the XLOOKUP function when you need to find things in a table or a range by row. Boost your productivity with this powerful function. We extract some of these data onto a daily printout of salient information. Read more here. Unlike older functions like VLOOKUP or HLOOKUP, XLOOKUP is more flexible and easier to use. What is the XLOOKUP Function in Excel The XLOOKUP function is a powerful tool for finding specific data within a range. 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. Moreover, you can check and follow the steps in this Implement accurate lookups with Excel XLOOKUP and XMATCH. Jun 29, 2021 · I want to do an xlookup where there are multiple matches and get excel to return the maximum value: e. Aug 20, 2025 · Wrapping Up In this tutorial, we explored multiple methods to pull data from another sheet in Excel based on criteria from classic options like VLOOKUP and INDEX / MATCH function to modern and dynamic ones like XLOOKUP, FILTER, and Power Query. 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. Nevertheless when I provide only one lookup_value it works well. Using XLOOKUP with Multiple Criteria is one of the most powerful and amazing formulas you can write in Excel. How can I fix this so it shows the correct placings? Jun 22, 2023 · Are there any limitations when using XLOOKUP with multiple criteria? XLOOKUP can only work when the criteria match exactly. These ways will help you to use XLOOKUP function. XLOOKUP offers two primary methods for handling multiple criteria searches: Boolean logic and Concatenation. 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. All of these work by identifying the first match and return that. Useful step-by-step example you can use right now. With "2-Day Air" in cell G6 and 72 in cell G7, XLOOKUP returns $45. Nov 14, 2023 · This tutorial explains how to use the XLOOKUP function in Excel to sum all matches in a range, including an example. It's way better than VLOOKUP or INDEX/MATCH. Quickly learn how to create an Excel XLookup return all matches formula. 00. Feb 6, 2020 · Learn all about the new Excel XLOOKUP Function. May 2, 2025 · To perform an approximate match lookup with multiple criteria, you can use the XLOOKUP function, with help from the IF function. 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. You have a table of items, with the following columns. 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 Nov 14, 2024 · Mastering Multi-Criteria Lookups in Excel with XLOOKUP and SUMPRODUCT When it comes to Excel, one of the most common (and sometimes trickiest) tasks is finding data based on multiple criteria. Apr 23, 2024 · XLOOKUP Google Sheets is a major upgrade over VLOOKUP and brings along lots of interesting customization. This article explores an advanced technique: using XLOOKUP to return all matches within a given dataset. Note: XLOOKUP performs an exact match by default, so match mode is not set. g. Jun 25, 2024 · XLOOKUP is a lookup function that searches a range or an array for a match and returns the corresponding item from a range or array in Excel. 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. In the example shown, the formula in G5 is: =XLOOKUP (1, (LEFT (B5:B16)="x")* (C5:C16="east")*NOT (MONTH (D5:D16)=4),B5:E16) With XLOOKUP's default settings for match_mode (exact) and search_mode (first to last) the formula matches the first record where 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. In this tutorial, we are going to learn it in detail. If no match is found, XLOOKUP can return an approximate match, if a specific match type is provided. 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 I have a spreadsheet where XLOOKUP will find multiple identical values in its lookup array, but the return array will have different values. Jan 29, 2025 · This article demonstrates how to use the XLOOKUP function in Excel 365. Which method do you prefer? Jun 14, 2024 · Learn how to use XLOOKUP in Excel to efficiently search & retrieve data from multiple columns simultaneously. 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. 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. Here is my test result: You can change the data range based on your requirement. I prefer the boolean array method and have used it a few times. Enter an equal sign and write the XLOOKUP formula. Apr 12, 2024 · Why Use XLOOKUP The XLOOKUP function searches a range or an array of data and returns the item corresponding to the first match. Then using his example put =B20# in the validation list. Most examples you find online will show something like this. I'm trying to show a list of placings, i. Multiple Values The XLOOKUP function in Excel 365/2021 can return multiple values. Here is the input data on Sheet1: ID Name 4003 Bob3 4003 Tom3 4003 Jim3 4004 Bob4 4004 Tom4 4004 Jim4 4005 Bob5 4005 Tom5 4 Subscribed 39 22K views 1 year ago #xlookup #datacleaning #exceltricks Excel LOOKUP formula to return multiple values (all matches) https://exceldashboardschool. 1. However, you can get around this by using Boolean logic or concatenation to instruct XLOOKUP to use multiple search criteria. Note the lookup_value in XLOOKUP is 1 since the logical expressions in lookup_array create an array of 1s and 0s. If we are talking about lookup functions in Excel, everyone knows that XLOOKUP is a Swiss knife in Excel. Left Lookup Instead of using INDEX and MATCH in Excel to perform a left lookup, simply use the XLOOKUP function. Aug 13, 2024 · The best way to use XLOOKUP with multiple criteria is to use Boolean logic to apply conditions. The XLOOKUP function in Excel handles vertical or horizontal arrays and supports exact matches, wildcards, and binary searches. 00, the price for a Medium Blue Hoodie.