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

Xlookup wildcard partial match multiple criteria Note: you can use the same approach Mar 25, 2022 · Microsoft Excel’s XLOOKUP() is powerful, but combined with wildcards, it’s also flexible. Nov 8, 2024 · Learn how to use XLOOKUP to match text values with the help of the 5th argument, wildcard character match mode. The XLOOKUP function in Excel handles vertical or horizontal arrays and supports exact matches, wildcards, and binary searches. 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. Get expert tips, ask questions, and share your love for all things Excel. It's way better than VLOOKUP or INDEX/MATCH. Jun 8, 2025 · Master Excel's XLOOKUP function with this comprehensive guide. XLOOKUP can look vertically and horizontally, to the left and right, and supports approximate and exact matching, as well as wildcards (* ?) for partial matches. Aug 2, 2024 · Step 2 – Combine the INDEX and the MATCH Functions for Multiple Criteria using a Wildcard in Excel Select E7. May 15, 2024 · To lookup data based on multiple complex criteria, you can use the XLOOKUP function with multiple expressions based on Boolean logic. Dec 8, 2022 · Hi,  I'm having an issue with partial matching text in the lookup cell of my X lookup 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. May 2, 2025 · To perform an approximate match lookup with multiple criteria, you can use the XLOOKUP function, with help from the IF function. What am I doing wrong? The normal XLOOKUP without "*"& &"*" works fine, it's just that when I put those around the lookup value, it gives me not found. Apr 24, 2024 · Whether you’re searching for partial matches, using multiple wildcard patterns, or exploring advanced lookup scenarios, mastering wildcard searches with XLOOKUP can significantly elevate your productivity and effectiveness in Excel. This Excel XLOOKUP Wildcard Tutorial is accompanied by an Excel workbook with the data and formulas I use. Nov 8, 2024 · Learn how to use XLOOKUP with multiple criteria by concatenating lookup values and lookup arrays inside the formula. Jun 18, 2025 · Learn how to use XLOOKUP function in Excel using this step-by-step tutorial with 10 practical examples and a free sample workbook. Example: XLOOKUP ("*search*", A2:A10, B2:B10) Check for leading or trailing spaces: Sometimes extra spaces in your data can prevent matches. 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. Enter the INDEX MATCH formula in the Formula bar. Here are a few things to check: Ensure correct wildcard usage: Use * to match any number of characters and ? to match a single character. 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. 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. The Google Sheets XLOOKUP with Multiple Criteria allows users to search for the desired value from the horizontal and Jan 17, 2021 · Advanced XLOOKUP function: Learn wildcard searches, "if not found" error catching and group classification in XLOOKUP. Includes downloadable file! 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. 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. 🕰️ Timestamps 00:00 Introduction 00:41 Contains criteria 02:54 Begins with criteria 04: Here are the 2 Best Ways to use XLOOKUP with multiple criteria in Excel. Nov 7, 2022 · This tutorial explains how to use the XLOOKUP function in Excel to return all matches, including an example. From simple lookups to complex data retrievals involving multiple criteria, XLOOKUP simplifies the process while improving accuracy and efficiency. xelplus Nov 3, 2025 · XLOOKUP comes with a wildcard search option in the 4th argument so you can explicitly request a search using wildcard characters. This comprehensive guide explores its multiple criteria feature, offering a dynamic approach to finding and extracting data, making complex queries simple and efficient. Jul 13, 2024 · XLOOKUP is a powerful Excel function that allows you to search for specific data in a range or table. Easy and step-by-step! Dec 31, 2024 · Master XLookup partial match techniques for error-free results, including wildcard, fuzzy, and approximate matching, to enhance data analysis and lookup accuracy in Excel. Read below for details Oct 9, 2025 · Learn how to use XLOOKUP for partial matches in Excel with wildcards, examples, and step-by-step formulas to find flexible text or code matches easily. 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. ⬇️ Download the file I used in the video from here: https://pages. 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. Step-by-step guide for all Excel versions. Note the lookup_value in XLOOKUP is 1 since the logical expressions in lookup_array create an array of 1s and 0s. In this tutorial, you will find examples of common lookup scenarios that you can achieve using match mode 3 in Excel. Wrapping Up XLOOKUP is a game-changer in Excel, offering flexibility and power for a wide range of data lookup tasks. 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. Feb 2, 2024 · This scenario, known as XLOOKUP with multiple criteria, can be addressed by implementing IF and Boolean logic in Google Sheets. Check how to use it with our practical guide. 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. Read on, or watch our video walkthrough, to learn how to use this modern lookup function to effectively replace VLOOKUP, HLOOKUP and INDEX-MATCH in your formulas. Did you know? May 20, 2023 · Retrieving a value using multiple criteria: =XLOOKUP (1, (B2=lookup_range)* (C2=lookup_range)*match_value,return_range) XLOOKUP is a versatile and powerful function in Excel that can help you search for and retrieve data more efficiently. 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. Nov 29, 2024 · Learn how to use XLOOKUP with multiple criteria in Excel. Sep 27, 2024 · Master Excel formula for partial matches using VLOOKUP, XLOOKUP, Filter and more Examples. Aug 13, 2024 · The best way to use XLOOKUP with multiple criteria is to use Boolean logic to apply conditions. May 11, 2024 · To perform a partial match with XLOOKUP, supply 2 for the match mode argument to allow wildcards. Mar 14, 2023 · The tutorial shows how to do multiple criteria vlookup with INDEX and MATCH in Excel. Feb 6, 2020 · Learn all about the new Excel XLOOKUP Function. Using wildcards with XLOOKUP enhances its flexibility, enabling you to perform partial matches and more complex searches. Feb 5, 2025 · In lieu of XLOOKUP, VLOOKUP with multiple criteria and INDEX-MATCH combinations serve as viable substitutes, exhibiting comparable versatility and efficacy, predominantly in the context of legacy spreadsheet applications. 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. Learn syntax, examples, and advanced techniques for modern lookup formulas that replace VLOOKUP and HLOOKUP. Nov 14, 2023 · Example: How to Use XLOOKUP with Wildcard in Excel Suppose we have the following dataset in Excel that contains information about points scored by various basketball players: Suppose we would like to look up the value in the Team column with the partial text “ock” and return the corresponding value from the Points column. This detailed guide includes examples, formulas, and tips to simplify complex lookups. 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. This article will explain how to use wildcards with XLOOKUP, providing step-by-step instructions, examples, and best practices. What Undercode Say XLOOKUP with the asterisk wildcard character is a versatile tool in Excel that can significantly enhance your data lookup capabilities. 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. The XLOOKUP function is available in Excel 2021 and later (including Excel 365). Jun 29, 2024 · Hi @OP! 👋 The issue might be due to wildcard matching behavior in XLOOKUP. You can use TRIM () on your lookup In this Microsoft Excel video tutorial I explain how to use wildcard criteria with the FILTER function. Elevate your spreadsheet skills with us! May 2, 2025 · The best way to use XMATCH with multiple criteria is to use Boolean logic to apply conditions. XLOOKUP is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. Sep 2, 2025 · XLOOKUP supports exact and approximate matching, lookups in vertical and horizontal ranges, and wildcards for partial matching. 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. Get this example workbook (for Jun 14, 2024 · Learn how to use XLOOKUP in Excel to efficiently search & retrieve data from multiple columns simultaneously. Jul 7, 2025 · Learn how to return all matching values in Excel using FILTER, INDEX, array formulas, and more. The TRANSPOSE function is optional and used here only to convert the result from XLOOKUP to a vertical array. See full list on exceldemy. 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 Oct 23, 2024 · I know that you can use a XLOOKUP with multiple criteria using something like this: =XLOOKUP($A2&$B2&$C2,Criteria!$A$2:$A$3&Criteria!$B$2:$B$3&Criteria!$C$2:$C$3,Criteria!E$2:E$3,0) The problem with this is that it wont work with the lines that only need to match with a single criteria, like the "A1 = A" that I mentioned. 00. Note: In older versions of Excel that do Oct 2, 2023 · If we need to perform a lookup with multiple criteria, see how to match multiple criteria in multiple columns. This is because the lookup_array is an array that contains only 1s and 0s. This capability enhances data analysis by enabling searches based on several conditions. 00, the price for a Medium Blue Hoodie. 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. I'm trying to use the partial text match/wildcard function in XLOOKUP but it's giving me not found everytime, even though the text values are there. Whether you’re performing forward or reverse lookups, handling partial matches, or dealing with multiple criteria, XLOOKUP offers a robust solution. It can look in any direction, return last match, perform v-lookup or h-lookup with multiple criteria, two-way lookup, and more. Here you will find 10 XLOOKUP examples + download file Aug 13, 2024 · To create a "contains substring" type lookup formula, you can use the XLOOKUP function with wildcards. Note that match_mode must be set to 2 to use wildcards with XLOOKUP. 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. With "2-Day Air" in cell G6 and 72 in cell G7, XLOOKUP returns $45. Jul 3, 2024 · Partial match string in excel is done by using the VLOOKUP, the XLOOKUP , the INDEX MATCH combo , IF OR, IF ISNUMBER SEARCH combo functions. Aug 19, 2025 · Learn how to look up partial text matches in Excel using VLOOKUP with wildcards, INDEX & MATCH combo, and IF for conditional results. = XLOOKUP ( B2, Contact[Name], Contact[Email], 2 ) In this example we use the * and ? character to search for partial matches in a name and then return the corresponding email address. XLOOKUP offers a more versatile and efficient approach compared to traditional functions like VLOOKUP and INDEX MATCH. We will learn how to perform partial matches and search for specific patterns within a dataset. com Nov 21, 2023 · The objective of this tutorial is to show how to use wildcards with the XLOOKUP and FILTER functions in Excel. Implement accurate lookups with Excel XLOOKUP and XMATCH. XLOOKUP can simplify complex data retrieval processes and save time by avoiding the need for data repositioning and manual scrolling. 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. This is helpful in various scenarios such as case-sensitive matching, partial word matching, specifying multiple search keys to match either of the first occurrences, and more. . trueA vibrant community of Excel enthusiasts. Boost your productivity with this powerful function. What's more, there are two methods you can use, each differing in complexity and versatility. Click here to learn about these 2 easy approaches! Apr 23, 2024 · XLOOKUP Google Sheets is a major upgrade over VLOOKUP and brings along lots of interesting customization. Feb 10, 2020 · XLOOKUP does more than just replace VLOOKUP! This post explains new functions such as wildcard matching, using multiple criteria and multiple matches. Formula examples are available for download. Apr 18, 2025 · Master the art of data retrieval with Google Sheets' XLOOKUP function, a powerful tool for precise searches. Note: This is a new feature and available only in beta version. Note the lookup_value in XMATCH is 1. 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 In this Excel XLOOKUP Wildcard Tutorial, you learn how to create an Excel XLOOKUP wildcard formula, where the XLOOKUP function considers wildcards when searching for a match. 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. Use the XLOOKUP function to find things in a table or range by row. 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. Oct 9, 2025 · Utilizing XLOOKUP with multiple criteria in Excel allows for more flexible data searching compared to traditional lookup functions. Jun 25, 2024 · Example 9 – Apply Excel XLOOKUP Function to Find Partial Match Using Wildcard In the movie sample dataset, we will find the genre of a given movie with a wildcard. 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. Read below for 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. Feb 2, 2024 · See how to use the XLOOKUP function for vertical and horizontal lookup in Excel.