XLOOKUP Function in Excel

In our previous blog post we have already learned about 2 most useful lookup functions: VLOOKUP and HLOOKUP. In this blog post, we’ll explore what XLOOKUP is, its syntax, why you should use it, its pros and cons, and provide some practical examples. The XLOOKUP function revolutionize the way we search and retrieve data.

XLOOKUP function is a modern and versatile replacement for traditional lookup functions in Excel. It allows you to find things in a table or range by row, regardless of whether the return column is on the left or right side. It’s like a search engine for your Excel data. Here’s what you need to know:

  • Syntax:

    =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

  • Arguments:

    • lookup_value: The value you want to search for.
    • lookup_array: The range or array to search.
    • return_array: The range or array from which to return results.
    • [if_not_found]: (Optional) Specify what to return if no match is found.
    • [match_mode]: (Optional) Specify the match type (exact, approximate, wildcard).
    • [search_mode]: (Optional) Specify the search direction.

            Why you should use XLOOKUP?

            • XLOOKUP can search both horizontally and vertically, unlike VLOOKUP and HLOOKUP.
            • It can return an array with multiple items, making it more versatile.
            • XLOOKUP supports wildcard characters (* and ?) for partial matches.

            Pros and Cons of XLOOKUP

            ProsCons
            Flexible and powerfulNot available in Excel 2016 and 2019
            Handles both horizontal and vertical dataRequires familiarity with new syntax
            Supports wildcards for text dataMay not be widely used yet
            Can return multiple results

            Conditions Before Using XLOOKUP

            1. Excel Version- XLOOKUP is not available in Excel 2016 and 2019. Ensure you are using Excel 365 or Excel 2019 (with the latest updates) to access this function.

            2. Syntax Familiarity- XLOOKUP introduces a new syntax. Familiarize yourself with the function’s arguments and usage.

            3. Data Structure- XLOOKUP works best with structured data in tables or arrays. Ensure your data is organized with clear headers and consistent formats.

            Examples

            (Data File)

            Example #1: Nested XLOOKUP

            For this example we have a table of sales data with product names and corresponding prices in different regions. You want to find the price for a specific product in a given region. Here’s how you can nest XLOOKUP functions:

            =XLOOKUP(H4,B3:E3,XLOOKUP(H3,A3:A13,B3:E13))

            Example #2: Exact and Partial Match

            XLOOKUP can perform both exact and approximate matches.
            Use the [match_mode] argument to specify the match type (0 for exact, 1 for approximate).

            2.1 Exact Match

            Here we have a list of employee IDs and their corresponding names. To find an employee’s name based on their ID, we will use:
            =XLOOKUP(E4,A4:A13,B4:B13,"Employee Not Found",0 )

            2.2 Partial Match

            Here we have a list of sales target and their incentives rate. To find the incentive for a sale that partially matches the search term, we will use:
            =XLOOKUP(L4,H4:H13,I4:I13,"No Incentives",-1 )
            Example #3: Wildcards for Text Data
            XLOOKUP supports wildcard characters (* and ?) for partial matches. Useful when dealing with text-based lookup values.
            Suppose you have a list of employee name and their location. You want to find the location of an employee but you don't remember his full name then, use:
            =XLOOKUP("*Sharma",A4:A13,B4:B13,"Not Found",2)
            Example #4: 
            Customizing Not Found Results
            Specify a custom text in double quotes (e.g., “Not Found”) for cases where no match is found.
            Use the [if_not_found] argument. You can also use a formula if required

            For this we  have an example with Sales target amount and incentives rate when they achieve those sales target

            A) Customizing Not Found Results as a Formula:

            You want to deduct Rs. 200 from the basic criteria incentive rate, i.e. Rs. 100. So, if the sales are below Rs. 50000, the final output should be Rs. 800. 
            Otherwise, it should show the rates given. 
            The formula is:
            =XLOOKUP(E5,A4:A13,B4:B13,B4-200,-1)

            B) Customizing Not Found Results as a Text Example:
            If the sales are below Rs. 50000, you want the output to be “No Incentives.”
            Otherwise, it should show the rates given.
            The formula is:
            =XLOOKUP(F5,A4:A13,B4:B13,"No Incentives",-1 )



            Example #5: Multiple Criteria Lookup
            XLOOKUP excels at handling multiple criteria. Use Boolean logic to apply conditions. 

            For this example we have a data for item details and we want to find price of an item with a specific size and color 

            =XLOOKUP(1, (A1:A11 = G1) * (B1:B11 = G2) * (C1:C11 = G3), D1:D11)
            This formula looks up a price based on Item, Size, and Color simultaneously.

            Example #6: Total Sales

            You can use formulas and operators to add, subtract, multiply, or divide values to obtain the total output as required.

            Suppose we have a sales data of employees in different region, and we want to find sales of an employee in all regions for this we have to add sales of all the regions (i.e. East, West, North and South).

            We can use this formula: =XLOOKUP(H3,A4:A13,B4:B13+C4:C13+D4:D13+E4:E13)

            Conclusion

            XLOOKUP is a game-changer in Excel. Embrace its flexibility, explore its capabilities. Whether you’re an Excel pro or a beginner, XLOOKUP is your new best friend! 

            Thank you for reading and happy learning.

            Comments

            Post a Comment

            Popular posts from this blog

            Introduction to Pivot Table in Excel - Advanced

            Image
            In the previous blog post, we learned the  Basics of Pivot Table  in Excel. In this blog post, we will explore some of the advanced features of pivot tables, such as slicers, timelines, calculated fields, pivot charts, managing relationships, etc. These features can help you change your existing pivot table and create different views of your data every time. Slicers Slicers are used to filter your data by selecting specific values from a field. For example in this data table , we have a column named Product (Crayons, Geometry Box, Paper, Pen, Stapler, Sticky Notes)  you can use a slicer to show only the data for a particular product or products.  Steps to add a Slicer: Select your pivot table ➡️ PivotTable Analyze ➡️ Click on "Insert Slicer" ➡️Choose the field for the slicer. A slicer window will appear, where you can select a field and a slicer will be inserted. You can select one option or multiselect by holding down the CTRL key while making your selections. You c...
            Read more

            Introduction to Pivot Table in Excel - Basics

            Image
            Pivot tables are one of the most powerful and useful features in Excel. Allowing us to quickly summarize, analyze, and explore large amounts of data in various ways. It is a special type of table that displays the summary of a data set and helps us in analyzing the data in seconds without knowing any formulas and functions. It consists of four areas: rows, columns, values, and filters. You can drag and drop different fields from your data set into these areas to create different views of your data. In this blog post, we will introduce the basics of pivot tables. Converting data to tabular data before inserting Pivot Table is an added advantage as this eliminates the need to frequently adjust the table range as new entries are added (select a cell from the data and click CTRL+T and then OK).  Additionally the following conditions need to be fulfilled: ✔ Each column should have unique headers, and the headers should be descriptive enough.       For ex. If Revenue...
            Read more