Xlookup match mode 2 in excel. If your data requires an approximate match (e.

Xlookup match mode 2 in excel 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. When match_mode is -1, XLOOKUP seeks an exact match or else the largest value less than or equal to lookup_value (6. Match-Mode can be one of 3 values: 0: Exact match, which is the default Implement accurate lookups with Excel XLOOKUP and XMATCH. Enter an equal sign and write the XLOOKUP formula. Jul 6, 2025 · Here’s the general syntax of the XLOOKUP function: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) We’ll focus on the match mode argument, which determines how Excel searches for the lookup value. 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. Follow the steps below: Select cell E3. Working from the inside out, the inner XLOOKUP is used to The Excel XLOOKUP function searches for a value, offering flexible matching options and improvements over VLOOKUP and HLOOKUP. Overlooking Exact Match: By default, XLOOKUP searches for an exact match. Nov 3, 2025 · XLOOKUP Can Search Last To First It’s pretty well known that VLOOKUP searches first to last and returns the first match. Apr 6, 2025 · Are you tired of using old lookup functions like VLOOKUP and HLOOKUP? Discover XLOOKUP Formula, Microsoft’s newest and most advanced lookup tool. Press ENTER. 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. Exact match: 0 (is also the Aug 3, 2024 · The XLOOKUP function in Excel is a powerful and versatile lookup function that allows you to search for a value in a range or array and return a corresponding value in another range or array. 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. The function returns the entire row or column based on the search direction. May 7, 2024 · 2 – wildcard match mode The formula returned “ Randolf ” who drives a Mitsubishi and is the first match from the top. Oct 2, 2023 · = XLOOKUP ( lookup_value ; lookup_array ; return_array ; if_not_found ; match_mode ; search_mode ) The lookup value can be either a text or a number, inputted directly into the function or as a cell reference. No need, to wrap it around any longer. The second occurrence of “Maurene Gillio” has been located, and it corresponds to the value “248. Apr 6, 2025 · Key Takeaways XLOOKUP is a powerful Excel function exclusively available for Microsoft 365 users. The XLOOKUP function in Excel handles vertical or horizontal arrays and supports exact matches, wildcards, and binary searches. It can look in any direction, return last match, perform v-lookup or h-lookup with multiple criteria, two-way lookup, and more. Aug 23, 2025 · Master Excel XLOOKUP function with our comprehensive guide. The following example resembles with the first (Basic Jan 8, 2025 · XLOOKUP is a powerful Excel function that can return multiple values in one cell. Behavior: Searches for an exact match; if not found, returns the next smaller item. If an exact match is not found, XLOOKUP returns #N/A. 2 – Wildcard character match allows you to use ? or * wildcards for inexact matches. XLOOKUP is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. This part works similar to the IFNA function. return_array: The range/column from which Jan 17, 2021 · Advanced XLOOKUP arguments IF NOT FOUND: If the search value was not found, this argument is returned. Jun 17, 2025 · Learn how to use XLOOKUP function in Excel using this step-by-step tutorial with 10 practical examples and a free sample workbook. Arguments Explained lookup_value: The value you want to search for. This function has many more advanced functionalities over the age-old lookup functions like INDEX/MATCH and VLOOKUP/HLOOKUP formula in excel. Final Result. If your data requires an approximate match (e. Note: XLOOKUP performs an exact match by default, so match mode is not set. Use the XLOOKUP function to find things in a table or range by row. What is XLOOKUP in Excel? The XLOOKUP function in Excel is being hailed as the replacement for both VLOOKUP and HLOOKUP, hence the “X” standing in for either character. Feb 26, 2024 · Discover how to use XLOOKUP in Excel with our comprehensive guide. 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 Search_Mode 1 – Search top to bottom (or left to right for horizontal lookup) Apr 4, 2025 · XLOOKUP with Approximate Match XLOOKUP can perform approximate matches, useful for finding closest values. 🤔 ABOUT THIS Lesson 👇 HOW TO USE XLOOKUP MATCH MODE BY EXCEL IN A MINUTE Welcome to Excel in a Minute!You have probably heard about approximate match and e Nov 19, 2024 · Hello, and welcome to this deep dive into the fascinating world of Excel’s XLOOKUP function—specifically using it with RegEx (Regular Expressions). If you have Excel 365 or Excel 2021, use XLOOKUP instead of VLOOKUP. Oct 9, 2025 · Return All Matches in Excel Using XLOOKUP and Other Functions 2 ways to use XLOOKUP with Multiple Criteria in Excel Understanding Match Mode in Excel XLOOKUP Function Top 8 Powerful Excel XLOOKUP Examples: Enhance Your Data Analysis Skills XLOOKUP is not working. 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 F5, copied down the table, is: =XLOOKUP(E5,qty,disc,,-1) where qty (B5:B13) and disc (C5:C13) are named ranges. So, if you want, you could consider the IFNA () function right inside the XLOOKUP. Using wildcards with XLOOKUP enhances its flexibility, enabling you to perform partial matches and more complex searches. Simplify complex lookups and enhance your data analysis skills with our expert tips. . This advanced lookup capability allows users to combine data from different columns into a single cell, making data analysis and reporting more efficient. Note that match_mode must be set to 2 to use wildcards with XLOOKUP. It replaces the older functions like VLOOKUP, HLOOKUP, and LOOKUP, offering more flexibility and functionality. In this article, we will discuss how to use the XLOOKUP function in Excel, its syntax, and various examples to illustrate its usage. Example Scenarios: Without using wildcards, XLOOKUP looks for exact matches only. XLOOKUP is a versatile and powerful function that can be used to lookup and retrieve data from a table or range in Excel. Step 2: Enter the Lookup Value and Lookup Array In the first argument of the XLOOKUP function, enter the lookup value (in our example, the last name is the lookup value). XLOOKUP can do that too. Click here for a step-by-step tutorial on how to use the XLOOKUP function in Excel! Jun 8, 2025 · Excel’s XLOOKUP function represents a revolutionary advancement in spreadsheet lookup capabilities, offering a modern alternative to traditional VLOOKUP and HLOOKUP functions. Match mode tells Excel what to do if XLOOKUP cannot find the lookup value in the lookup range. Jan 24, 2023 · To do this, simply start typing “=XLOOKUP ” in the cell where you want the result to appear, and Excel will automatically show you the function’s arguments. It is a more advanced and flexible alternative to the older VLOOKUP and HLOOKUP functions, and it can handle both vertical and horizontal lookups. The XLOOKUP function is easier to use and has some additional advantages. Oct 9, 2025 · Behavior: This default mode searches for an exact match of the lookup value in the lookup array. In this post, we’ll cover the basics of XLOOKUP, explore the new RegEx match mode, and demonstrate some practical examples. XLOOKUP is a versatile and advanced addition to the Excel lookup functions. [search_mode] (optional): Defines the search order within the lookup_array (defaults to 1 – search first to last). The default value is 1. May 11, 2024 · To perform a partial match with XLOOKUP, supply 2 for the match mode argument to allow wildcards. This powerful function simplifies data retrieval tasks while providing enhanced flexibility and functionality that addresses many limitations of its predecessors. Note: In older versions of Excel that do Jan 7, 2021 · Return array The XLOOKUP function can also return multiple values as an array. Feb 21, 2020 · One of XLOOKUP's features is the ability to lookup and return an entire row or column. Jan 31, 2025 · Now you can take advantage of regex within the existing XLOOKUP and XMATCH functions, by using the new [match_mode] = 3 and a regex pattern as the lookup_value. Mar 25, 2022 · Microsoft Excel’s XLOOKUP() is powerful, but combined with wildcards, it’s also flexible. Now, what if you want to look up the first match from the bottom? In this case, choose a respective search mode. The XLOOKUP function in Excel allows an optional match mode argument, which is the fifth argument in the XLOOKUP function. Select the lookup array. 1. Finally, the search_mode argument is set to 1, which means the function will search from the first item to the last. g. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. While the syntax might seem complex at first glance, each element plays a crucial role in customizing your XLOOKUP formula for specific needs. Excel XLOOKUP search mode The [search_mode] is the sixth parameter in the XLOOKUP formula. The inner XLOOKUP returns a result to the outer XLOOKUP, which returns a final result. The 5th argument of XLOOKUP allows us to Search last-to-first. -1 performs an exact match, 0 performs an approximate match, and 1 performs an exact or next smaller match. Jan 24, 2025 · To find the Last Name of the first student whose First Name contains “oh”, we can use XLOOKUP with wildcards by setting the match mode to 2. Perfect for beginners and pros alike, learn syntax, examples, and advanced. Assumes the lookup array is sorted in descending order. Aug 14, 2025 · XLOOKUP Formula in Excel with Example =XLOOKUP (lookup_value, lookup_array, return_array, [if _not_found], [match_mode], [search_mode]) XLOOKUP Formula Explanation =XLOOKUP (search for this value, in this range, and Return a match from this range). The XLOOKUP function is much more functional than the VLOOKUP function which only searches for the value in the first column of a table, and it's simpler to use than the combination INDEX + MATCH which requires using 2 functions. The XLOOKUP formula finds the closest match below 85 (match_mode -1) and returns the appropriate grade. But XLOOKUP can also search last to first and return the last match. Nov 12, 2024 · Unlock the power of two-way XLOOKUP in Excel. It is denoted as a numeral from -2 to 2 with 0 excluded: 1 Aug 29, 2024 · 1. 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 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. In the worksheet shown, the formula in F5 looks like this: =XLOOKUP("[A-Z]{3}"&F4&"[A-Z]{2}",B5:B16,C5:C16,,3) This formula matches the number entered in cell F4 against the product codes in column B using a regex pattern. The options are -1, 0, and 1. Streamline data management and unlock new possibilities! Nov 8, 2024 · Learn how to use XLOOKUP to match text values with the help of the 5th argument, wildcard character match mode. May 26, 2024 · Defaults to 0 (exact match). Make a reference to the lookup value. Like its predecessors, XLOOKUP looks for a value in a table and returns a matching result. This article will explain how to use wildcards with XLOOKUP, providing step-by-step instructions, examples, and best practices. Key Takeaways Introduction to XLOOKUP Formula In the world of Microsoft Excel, a new function […] Mar 18, 2021 · The excel XLOOKUP function is a newly introduced lookup formula/function in excel. It's really cool and I explain more about wildcards below. 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. The XLOOKUP function syntax includes parameters like lookup_value, lookup_array, return_array, if_not_found, match_mode, and search_mode. match_mode: The type of match you want to perform. Aug 29, 2019 · The match_mode zero (0) returns #N/A because there is no exact match. Introduced in 2019, XLOOKUP is an upgrade to the VLOOKUP and HLOOKUP functions, providing more flexibility and functionality. Try these 5 Proven Solutions. Partial matches are useful when your lookup value doesn’t exactly match the lookup array entries. Mar 20, 2025 · What is XLOOKUP? XLOOKUP helps you find a value in a range or array. Feb 6, 2020 · Learn all about the new Excel XLOOKUP Function. ฟังก์ชัน XLOOKUP ใน Excel เป็นฟังก์ชันที่ถูกออกแบบมาเพื่อแทนที่ VLOOKUP, HLOOKUP และ INDEX+MATCH โดยทำให้การค้นหาข้อมูลในแนวตั้งหรือแนวนอนง่ายและยืดหยุ่นมากขึ้น The new Excel XLOOKUP Function replaces the need for VLOOKUP, HLOOKUP, INDEX & MATCH and more. Note that when using wildcards, the match mode parameter is required and it has to be set to 2. Nov 8, 2024 · This guide will explain how to find the 2nd match or the nth occurrence in an array using the XLOOKUP function. XLOOKUP can look vertically and horizontally, to the left and right, and supports approximate and exact matching, as well as wildcards (* ?) for partial matches. 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 The match_mode argument is set to 1, which means the function will look for an exact match, and if it can't find one, it returns the next larger item. ” You can also use this function to find the “n-th” match. It's way better than VLOOKUP or INDEX/MATCH. It’s available in Office 365. , interpolation), ensure you adjust the `match_mode` argument accordingly. Syntax Jan 4, 2025 · To use a regex pattern in an XLOOKUP formula, you can enable "regex match" as the match mode and then provide a regex pattern in the lookup value. With Excel’s dynamic arrays feature, all values in the return array will be populated (spilled) into multiple cells. Aug 13, 2024 · To create a "contains substring" type lookup formula, you can use the XLOOKUP function with wildcards. This feature can be used to nest one XLOOKUP inside another to perform a two-way lookup. The table shows score ranges and corresponding grades. 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. With the number 56 in cell Dec 23, 2020 · To use XLOOKUP to find an approximate match, supply the match_mode explicitly in the fourth argument. Oct 28, 2024 · The XLOOKUP function in Excel is one of the most powerful tools for searching and retrieving data. Read more here. This guide will show you how to use XLOOKUP to make your data analysis and reporting more efficient. lookup_array: The range/column where Excel will search. 2. This example demonstrates grade lookup based on score ranges. The XLOOKUP function below looks up the value 53 (first argument) in the range B3:B9 (second argument). Sep 4, 2019 · XLOOKUP does have an option [match_mode] argument that allows us to do approximate/closest match OR use wildcard characters. 5). Here you will find 10 XLOOKUP examples + download file 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. Feb 2, 2024 · See how to use the XLOOKUP function for vertical and horizontal lookup in Excel. Jul 11, 2024 · If this parameter is omitted, XLOOKUP will return the value in the same column as the lookup_value in the lookup_array. Exact Match By default, the XLOOKUP function in Excel 365/2021 performs an exact match. But whereas the other lookup functions require complex pairing with other functions like INDEX/MATCH to search in different areas 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. Jul 13, 2024 · XLOOKUP is a powerful Excel function that allows you to search for specific data in a range or table. It's better than VLOOKUP, HLOOKUP, and some uses of INDEX-MATCH. To do this, you need to supply a 2-dimensional array or range. XLOOKUP offers two primary methods for handling multiple criteria searches: Boolean logic and Concatenation. Oct 9, 2025 · In this guide, we will show you exactly how to use XLOOKUP for partial matches with step-by-step explanations, practical examples, and formula variations. We don’t need to sort our data in a different order to do this. MATCH MODE: With the match mode you can define in what case Excel should find your search value. Jun 10, 2024 · Use the XLOOKUP function to find the second match in Excel. Apr 10, 2023 · XLOOKUP Match_Mode 0 – Exact match will only find exact matches 1 (-1) – Will perform an exact match or find the next largest (smallest) item.