Xlookup with multiple criteria and wildcard. Check how to use it with our practical guide.

Xlookup with multiple criteria and wildcard In this video, you will learn how to use Xlookup, Index/ Match, Index/ Xmatch and Vlookup functions to perform vertical and wildcard match based on multiple criteria. We want to use it to find the salary of an employee in a specific department. So my question is, can you add wildcards to the following formula? =XLOOKUP (1, ("Deliver"=C$4:Q$4)* (Dates>=TODAY ()),C$3:Q$3,,2,1) I would like the ability to get a return on Jul 16, 2025 · To advance your Xlookup skills, consider learning how to use Multiple Criteria, Wildcards, and Arrays. Useful step-by-step example you can use right now. Third, you'll learn how to use the xlookup with multiple conditions to replace the index match. = XLOOKUP ( B2 & C2, Contacts[First] & Contacts[Last], Contacts[Email] ) In this example, we want to search for a contact’s email address based on their first and last name. Apr 7, 2025 · Master Excel's XLOOKUP function with 10 practical examples for exact matches, wildcards, multi-criteria lookups, and more, to improve your spreadsheet skills. trueA vibrant community of Excel enthusiasts. It's more powerful and easier to use than VLOOKUP and HLOOKUP. 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. Aug 25, 2022 · Example 5: Wildcard Match XLOOKUP in Google Sheets supports three wildcards, *, ?, and ~. May 13, 2024 · To use XLOOKUP with multiple logical, build expressions with boolean logic and then look for the number 1. In the example shown, the formula in H8 is: =XMATCH(1,(B5:B15=H5)*(C5:C15=H6)*(D5:D15=H7)) The result is 6, since the sixth row in the data contains a Medium Blue Hoodie. To perform a multi-criteria lookup with XLOOKUP in Google Sheets, you can combine it with the ARRAYFORMULA. Nov 3, 2025 · Search Multiple Criteria With XLOOKUP We can take advantage of the new calculation engine in Excel along with XLOOKUP to easily lookup based on multiple criteria. Aug 29, 2024 · 1. Among its many powerful functions—such as VLOOKUP, HLOOKUP, INDEX, and MATCH—the more recent addition, XLOOKUP, has revolutionized how users perform lookup tasks, especially with its intuitive syntax, backward compatibility, and enhanced capabilities. We will learn how to perform partial matches and search for specific patterns within a dataset. Dec 22, 2022 · XLOOKUP Function is a versatile lookup function that performs activities such as lookup to find values in vertical and horizontal ranges, approximate & exact matches lookup, wildcards lookup. Enter an equal sign and write the XLOOKUP formula. The FILTER function allows you to condense a range of data based on criteria. 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 Learn how to use XLOOKUP with Multiple Criteria using concatenate lookup values and arrays. For example, to find the sales figure for a specific product in a particular region, you can nest multiple XLOOKUP functions to refine your search. Steps: Enter the following formula in Cell G7. Boost your productivity with this powerful function. 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. Read below for details May 20, 2023 · 5. We'll use examples to help you use it in your Excel projects. Note: see below for an equivalent formula based on INDEX and MATCH. Elevate your spreadsheet skills with us! 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. Sep 8, 2023 · Unlock the true power of the XLOOKUP function by being able to create lookups with Multiple Criteria. May 26, 2024 · Using XLOOKUP with an Array Formula to Handle Multiple Criteria: When working with multiple criteria in Excel, traditionally, users might create a helper column to combine these criteria into a single value that can be looked up. Add multiple criteria to your search. Definition of Multiple Criteria XLOOKUP’s Apr 16, 2025 · Learn how to master the XLOOKUP function with multiple criteria. Get expert tips, ask questions, and share your love for all things Excel. Master this function to perform advanced lookups, merge data, and handle complex conditions effortlessly. Feb 5, 2025 · Understanding XLOOKUP Multiple Criteria Google Sheets The XLOOKUP function in Google Sheets transcends basic searches, empowering users to conduct intricate data analysis. Learn how to enhance your spreadsheet skills and boost productivity with this essential guide. In this guide, we're going to show you how to use Wildcard criteria in Excel formulas. Task: 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. Sep 18, 2024 · Unleash the power of XLOOKUP with multiple criteria in Google Sheets. On the Recipe Card, if I enter 1 on the Aug 13, 2024 · To create a "contains substring" type lookup formula, you can use the XLOOKUP function with wildcards. XLOOKUP is a game-changer in Excel, offering flexibility and power for a wide range of data lookup tasks. Note that match_mode must be set to 2 to use wildcards with XLOOKUP. Unlike these traditional functions, XLOOKUP can search both vertically and horizontally, making it ideal for retrieving specific values from any range in a table. com 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. Note the lookup_value in XMATCH is 1. Feb 2, 2024 · See how to use the XLOOKUP function for vertical and horizontal lookup in Excel. It's way better than VLOOKUP or INDEX/MATCH. Includes structured references, performance tips, and troubleshooting. This powerful function is a must-have for any Google Sheets user. You can use TRIM () on your lookup Apr 30, 2024 · Method 3 – Applying Excel XLOOKUP to Lookup a Partial Text Match with a Wildcard Character The XLOOKUP function searches a range or an array for a match and returns the corresponding item from a second range or array. If your version of Excel does not support XLOOKUP, read how to use the VLOOKUP instead. Implement accurate lookups with Excel XLOOKUP and XMATCH. Jul 13, 2024 · Using wildcards with XLOOKUP enhances its flexibility, enabling you to perform partial matches and more complex searches. Read below for 2 days ago · How to Use XLOOKUP Function with Multiple Criteria in Excel Excel has long been the go-to tool for data analysis, management, and reporting. 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. 3. May 15, 2024 · To lookup data based on multiple complex criteria, you can use the XLOOKUP function with multiple expressions based on Boolean logic. Nov 8, 2024 · Example 5: Multiple criteria lookup using XLOOKUP with nested IF statements. 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. Dynamic Array Support: With XLOOKUP, you can return multiple results at once in a dynamic manner. Select the lookup array. Check how to use it with our practical guide. What Learn how to use XLOOKUP to find multiple criteria in Google Sheets with this step-by-step guide. With these advanced techniques, you can perform more complex searches and return more detailed information. Jan 4, 2025 · What's happening here is that XLOOKUP is matching the first product code that contains 56, KP563MN. Jun 14, 2024 · Learn how to use XLOOKUP in Excel to efficiently search & retrieve data from multiple columns simultaneously. Type the formula =XLOOKUP (1, (criteria1=lookup_array)* (criteria2=lookup_array)* (criteria3=lookup_array)*match_result,return The XLOOKUP function in Excel handles vertical or horizontal arrays and supports exact matches, wildcards, and binary searches. This article will explain how to use wildcards with XLOOKUP, providing step-by-step instructions, examples, and best practices. Note the lookup_value in XLOOKUP is 1 since the logical expressions in lookup_array create an array of 1s and 0s. Sep 27, 2024 · Master Excel formula for partial matches using VLOOKUP, XLOOKUP, Filter and more Examples. Let’s show you the advanced lookup techniques beyond VLOOKUP by using XLOOKUP, and INDEX-MATCH-MATCH to leverage them effectively. Here you will find 10 XLOOKUP examples + download file Jan 6, 2020 · Wildcard represents one or more other characters. XMATCH supports approximate and exact matching, reverse search, and wildcards (* ?) for partial matches. Jun 4, 2025 · The Excel XMATCH function performs a lookup and returns a position of a value in a range. 00. In the example shown, XLOOKUP is used to look up the first sale to Chicago over $250. 🚀 Unlock hidden Excel tricks and hacks, and become a time-saving Excel pro 👉 excel Jan 6, 2025 · To look up email by ID: =XLOOKUP (1002, A2:A4, C2:C4, “Not Found”, 0, 1) Using Wildcards and Approximate Matches Command: Create a flexible XLOOKUP with wildcards Use “*” for multiple characters or “?” for single character matches Set match_mode to 2 for wildcard support Example: =XLOOKUP (“A10*”, A2:A4, B2:B4, “Not Found”, 2) Build Complex Lookup Formulas for Large Oct 2, 2023 · If we need to perform a lookup with multiple criteria, see how to match multiple criteria in multiple columns. Jun 29, 2024 · Hi @OP! 👋 The issue might be due to wildcard matching behavior in XLOOKUP. Easy and step-by-step! 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. Jan 17, 2021 · Advanced XLOOKUP function: Learn wildcard searches, "if not found" error catching and group classification in XLOOKUP. To combine columns of multiple rows with the & operator, you need to wrap the expression in arrayformula(), like this: =xlookup( C3 & "→" & C4, Then, you'll learn how to use the XLOOKUP with a wildcard for approximate matches. I'm wondering if there is a way around this. Here’s how: Select the cell where you want to retrieve the lookup value. XLOOKUP can simplify complex data retrieval processes and save time by avoiding the need for data repositioning and manual scrolling. The question mark ? matches exactly one character. Method 1: Using Boolean Logic in XLOOKUP Combine multiple criteria using Boolean (AND) logic within the XLOOKUP function: Example: Lookup the “Price” based on “Product Nov 21, 2023 · The objective of this tutorial is to show how to use wildcards with the XLOOKUP and FILTER functions in Excel. The tilde ~ is an escape character that lets you search for a * or ?, instead of using them as wildcards. In the example shown, the formula in H7 is: =TRANSPOSE ( (XLOOKUP (H4,D5:D15,B5:E15,"Not found",2))) which performs a wildcard match with the value in H4 and returns all 4 fields as the result. Note: In older versions of Excel that do This video shows how we can use wildcards (partial matches) with the XLOOKUP and FILTER functions. Additionally, my lookup values need a wildcard search, and I also need Mar 20, 2025 · Microsoft Excel has changed a lot over the years. Apr 6, 2025 · XLOOKUP offers advanced features like partial matches, multiple search criteria, and nested queries, making it a more robust solution than traditional lookup functions. I want to be able to look up the Item by inputting the Inventory Number as well as have the Spreadsheet look up the price per unit or measure. A vibrant community of Excel enthusiasts. XLOOKUP becomes more powerful in Excel with the new match mode 3, which enables using regex patterns as the search key. May 2, 2025 · To perform an approximate match lookup with multiple criteria, you can use the XLOOKUP function, with help from the IF function. From simple lookups to complex data retrievals involving multiple criteria, XLOOKUP simplifies the process while improving accuracy and efficiency. XLOOKUP is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. Using XLOOKUP with multiple criteria If you want to search for values based on multiple criteria, you can use XLOOKUP with the IF function. Example 2: XLOOKUP with Multiple Criteria Suppose we have a table with employee names, departments, and salaries. In this post, we'll show you how to use the XLOOKUP function step by step. 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. Mar 25, 2022 · Microsoft Excel’s XLOOKUP () is powerful, but combined with wildcards, it’s also flexible. 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. Example: XLOOKUP ("*search*", A2:A10, B2:B10) Check for leading or trailing spaces: Sometimes extra spaces in your data can prevent matches. The Google Sheets XLOOKUP with Multiple Criteria allows users to search for the desired value from the horizontal and May 2, 2025 · The best way to use XMATCH with multiple criteria is to use Boolean logic to apply conditions. Aug 8, 2016 · index match with wildcards and multiple criteria Asked 9 years, 3 months ago Modified 6 years, 5 months ago Viewed 30k times Key Features of XLOOKUP Versatility: XLOOKUP can search both vertically and horizontally, which can reduce the need for multiple formulas. This is because the lookup_array is an array that contains only 1s and 0s. I am writing a Recipe "Bible" for a Kitchen that I run. Lookup functions are great for finding values that match another value. Whether you’re performing forward or reverse lookups, handling partial matches, or dealing with multiple criteria, XLOOKUP offers a robust solution. 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. Nov 8, 2024 · Learn how to use XLOOKUP to match text values with the help of the 5th argument, wildcard character match mode. Note: XLOOKUP is not case-sensitive. Use the XLOOKUP function to find things in a table or range by row. In the example shown, the formula in G5 is: =XLOOKUP (1, (data [Color]="red")+ (data [Color]="pink"),data) where data is the name of the Excel Table in the range B5:E14. Aug 13, 2024 · The best way to use XLOOKUP with multiple criteria is to use Boolean logic to apply conditions. 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. Apr 23, 2024 · XLOOKUP Google Sheets is a major upgrade over VLOOKUP and brings along lots of interesting customization. Lookup based on multiple criteria including wildcard and TODAY date Hey all, First time working with XLOOKUP and have hit a bit of a snag and looking for some assistance, and not even sure if this is the correct formula I'm looking for. Is there any way I can do this? It looks like the wildcard option in Xlookup only works on the lookup value, not the lookup array. This capability enhances data analysis by enabling searches based on several conditions. This scenario, known as XLOOKUP with multiple criteria, can be addressed by implementing IF and Boolean logic in Google Sheets. May 16, 2024 · To configure XLOOKUP with Boolean OR logic, use a lookup value of 1 with a logical expression based on addition. Standard XLOOKUP behavior when there are multiple matches. Mar 14, 2023 · Everything you need to know about wildcards: what wildcard characters Excel recognizes, and how to best use them with built-in features and in formulas with text and numbers. Now, we will set the name of the league and the status as criteria and look for the corresponding Team. This guide will teach you to efficiently handle complex lookups, enhancing your data analysis and providing powerful insights. Quickly learn how to create an Excel XLOOKUP wildcard formula. This capability is pivotal for refining data retrieval processes. Oct 9, 2025 · Utilizing XLOOKUP with multiple criteria in Excel allows for more flexible data searching compared to traditional lookup functions. This detailed guide includes examples, formulas, and tips to simplify complex lookups. In the example shown, the formula in F5 is: XLOOKUP ("*"&G4&"*",data [Title],data,,2) Where G4 contains a partial string to look for and data is an Excel Table in the range B5:D16. 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 9, 2025 · Combining Multiple Criteria: With XLOOKUP, you can combine multiple criteria without complex array formulas. Unlike its predecessor, VLOOKUP, XLOOKUP offers several advantages, including the ability to search in any direction, handle multiple criteria, and return array results. XLOOKUP can handle arrays natively, making it possible to search values or cells with multiple criteria. I'm trying to perform a lookup on a table where some of the records in the lookup table have a wildcard- for example The lookup value is something like 300-10-20 and the lookup array has 100-10-10, 200-10-10 and 300-10-*. Master advanced lookup techniques, nested formulas, and practical examples Oct 25, 2024 · The function supports lookups based on multiple criteria, wildcard characters for flexible search criteria, and can return multiple values from a single formula. Nov 29, 2024 · Learn how to use XLOOKUP with multiple criteria in Excel. Discover the art of XLOOKUP and unlock its full potential. Wildcards only work with text. Here is a list of the most common functions that do: AVERAGEIF, AVERAGEIFS COUNTIF, COUNTIFS MATCH MAXIFS, MINIFS SEARCH SUMIF, SUMIFS VLOOKUP, HLOOKUP XLOOKUP XMATCH Wildcards in Excel's Find and . It is a more robust and flexible successor to the MATCH function. Nov 8, 2024 · Learn how to use XLOOKUP with multiple criteria by concatenating lookup values and lookup arrays inside the formula. Here are a few things to check: Ensure correct wildcard usage: Use * to match any number of characters and ? to match a single character. Note: you can use the same approach 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 examples below, you will learn to use XLOOKUP to perform horizontal lookups, search using multiple criteria, return multiple values, search using wildcards, and search using named ranges. 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. Sep 11, 2024 · We can use the following formula: =XLOOKUP(A2, A:B, 2) Where A2 is the cell containing the employee name, and A:B is the range of cells containing the employee names and salaries. I am currently using a Xlookup formula to gather the Item by the Inventory Number, but not sure how to write the other formula to look up the price per unit. This advanced lookup capability allows users to combine data from different columns into a single cell, making data analysis and reporting more efficient. 00, the price for a Medium Blue Hoodie. Excel's XLOOKUP function is the newer and better versio Lookup based on multiple criteria including wildcard and TODAY date Hey all, First time working with XLOOKUP and have hit a bit of a snag and looking for some assistance, and not even sure if this is the correct formula I'm looking for. Make a reference to the lookup value. May 5, 2021 · Hi, I am having trouble with adding a wildcard to my Xlookup formula. The star * matches zero or more characters. More general information on formula criteria here. Oct 16, 2023 · By combining SUMIFS with XLOOKUP, we can then sum all of the values that meet multiple criteria in different rows and columns and do this in a simple way, avoiding complex and resource-intensive array formulas. Let’s say you have a list of products, colors, and stock levels and want to find the stock level for a specific product and color combination. Aug 14, 2025 · The XLOOKUP function in Excel is a powerful and flexible tool designed to replace older lookup functions like VLOOKUP and HLOOKUP. XLOOKUP allows you to search with multiple criteria. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. Multiple criteria enable the function to search and retrieve data based on multiple conditions concurrently. For numeric data, you can use logical operators. Includes downloadable file! Nov 14, 2023 · This tutorial explains how to use the XLOOKUP function in Excel to sum all matches in a range, including an example. For other interesting applications, see how to combine SUMIFS with XLOOKUP in order to sum all of the values that meet multiple criteria in different rows and columns. What Undercode Say XLOOKUP with the asterisk wildcard character is a versatile tool in Excel that can significantly enhance your data lookup capabilities. Thanks to XLOOKUP () this Nov 14, 2023 · This tutorial explains how to use the XLOOKUP function in Excel with wildcard characters, including an example. May 11, 2024 · To perform a partial match with XLOOKUP, supply 2 for the match mode argument to allow wildcards. Apr 6, 2025 · Learn how to use XLOOKUP in Excel with multiple criteria. Read more here. With added benefits like default exact matching and the ability to handle errors more gracefully Jan 21, 2025 · Learn how to perform Excel multiple column lookups with ease using XLOOKUP, FILTER, SUMPRODUCT, and Boolean logic techniques. Nov 8, 2024 · Learn how to use XLOOKUP with logical criteria to build useful formulas (expressions) based on boolean logic. Jan 8, 2025 · XLOOKUP is a powerful Excel function that can return multiple values in one cell. See full list on exceldemy. Apr 24, 2024 · XLOOKUP is a dynamic function introduced in Excel that simplifies the process of searching for and retrieving data within a worksheet or a range of cells. Nov 7, 2022 · This tutorial explains how to use the XLOOKUP function in Excel to return all matches, including an example. Let's review the XLOOKUP Function and all you need to know about it. With XLOOKUP, you can quickly and easily find data in a table or range, even if the data is sorted or filtered. 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. Can we make XLOOKUP find the right code? Well, we could take the wildcard match idea a bit further by switching from "*" to the "?" like this: = XLOOKUP ("???56??",B5:B16,C5:C16,,2 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. May 7, 2024 · Excel XLOOKUP is a function that makes a look up to the left or to the right, top to bottom or bottom to top, and allows for wildcards. It can look in any direction, return last match, perform v-lookup or h-lookup with multiple criteria, two-way lookup, and more. Dec 2, 2024 · I have a project where I have been using XLOOKUP to pull information from one sheet into another, but my problem is the following: To see if there is a match, I have to check three different lookup values to find information and essentially run the function three different times. With "2-Day Air" in cell G6 and 72 in cell G7, XLOOKUP returns $45. Jun 25, 2024 · Example 4 – Apply the Excel XLOOKUP Function for Multiple Criteria We have selected a sample dataset of the roll of honor of 5 European leagues. Most errors in our XLOOKUP Formulas are related to XLOOKUP’s properties and criteria. Feb 6, 2020 · Learn all about the new Excel XLOOKUP Function. Its intuitive syntax and flexibility make it a preferred choice for Oct 19, 2025 · XLOOKUP multiple conditions are summarized in one article, including Boolean product (AND), string concatenation (with caution), OR/wildcards, and approximate matches + multiple conditions (IF filtering). Elevate your spreadsheet skills with us! Sep 2, 2025 · XLOOKUP lets you return multiple values. When it is using the exact text "Deliver" or a cell reference it works fine but as soon as I try to add a * wildcard before or after it errors out. Functions that support wildcards Not all functions allow wildcards. Now that you know how to use XLOOKUP to find an exact match for a single value, it’s time to learn about different ways of using it. 2. You can manipulate the result_range perimeter in your XLOOKUP formula to pull an entire column or row of data related to your lookup value. The XLOOKUP function is a big improvement. The formula in G6 is: =XLOOKUP(1,(D5:D14="chicago")*(E5:E14>250),B5:B14) which returns 0347, the order number of the first record that meets the supplied criteria. Feb 9, 2023 · This tutorial will demonstrate how to debug XLOOKUP formulas in Excel. gmpc qflmod hrutqrjn fveq rciit inyuufvk obe zxmy grwz lyjir nwklj pklu nhf bjdcpchk opjpmlkwi