Scribd Logo
Upload

Welcome to Scribd!

  • 2 views

    XLOOKUP

    Uploaded by

    Worth It

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    XLOOKUP

    Uploaded by

    Worth It
    2 views16 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    2 views16 pages

    XLOOKUP

    Uploaded by

    Worth It

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 16

    #1 EXCEL TRAINING

    Comprehensive
    Guide to
    XLOOKUP

    Jacek Cetnarski
    XLOOKUP

    COMPREHENSIVE GUIDE TO XLOOKUP

    Say goodbye to the VLOOKUP function and hello to the new


    XLOOKUP function! It is a genuine alternative to VLOOKUP,
    HLOOKUP, LOOKUP, INDEX and MATCH. Once you know how
    to use XLOOKUP, you will never want to go back to VLOOKUP.
    This new feature has more options and is characterized by its
    flexibility, efficiency, and user-friendliness.”

    www.eexcel.co.uk 1
    XLOOKUP

    SYNTAX

    =XLOOKUP
    (Lookup_value ,Lookup_array ,Return_array ,
    [If_not _found] ,[Match_mode] ,[Search_mode] )

    1. Lookup_value
    2. Lookup_array
    3. Return_array
    4. [If_not_found]
    5. [Match_mode] – default FALSE – Exact match

    6. [Search_mode] – default TRUE – First to last

    [] – optional arguments

    www.eexcel.co.uk 2
    XLOOKUP

    Simple example

    How to return the Name based of Surname?


    1st argument Lookup_value C2
    2nd argument Lookup_array (Surname) C15:C16
    3rd argument Return_array (Name) B15:B16
    The ranges must correspond. Please note that the return array
    is on the left side. The remaining arguments are not mandatory.
    By default, it returns the error #NA if there is no match. Find
    the exact match and the first match from top to bottom.

    =XLOOKUP(B2,C5:C16,B5:B16)

    www.eexcel.co.uk 3
    XLOOKUP

    Benefits of XLOOKUP over VLOOKUP

    ✓ The searched result in the column may be placed to the left


    of the column with the searched value.
    ✓ Changes in the structure of the table, such as inserting or
    removing columns, will not affect the result.
    ✓ Only relevant ranges are selected, not necessarily the entire
    table.
    ✓ The function can return a specified value if the searched
    value is not found. Use the following formula:
    =XLOOKUP(Lookup_value, Lookup_array, Lookup_return, "Not
    Found")
    Alternatively, you can use a nested VLOOKUP with error
    handling:
    =IFERROR(VLOOKUP(Lookup_value, Table_array, Col_num, 0),
    "Not Found")
    ✓ By default, the function performs an exact match, which is
    the most used option.
    ✓ If you want to find the closest (approximate) value, use the
    5th argument without any sorting requirement.
    ✓ If you want to return the nearest higher value, declare (1) in
    the 5th argument without sorting the data.
    ✓ Wildcard support is enabled when you declare (2) in the 5th
    argument; otherwise, wildcard symbols do not apply.
    ✓ You can search for the last value using (-1) in the 6th
    argument.
    ✓ Binary search is performed when you declare (2) or (-2) in
    the 6th argument. The data must be sorted in ascending or
    descending order.
    ✓ The function can perform searches vertically and horizontally
    if the ranges are marked accordingly.
    ✓ It provides efficient and faster functionality compared to
    other functions.
    www.eexcel.co.uk 4
    XLOOKUP

    LOOKUP MULTIPLE FIELDS

    &
    Multiple Criteria Using the concatenate operator &

    You can use the concatenate operator & to retrieve items from a
    table using multiple criteria.

    1st argument Lookup_value - B2&C2

    2nd argument Lookup_array - B5:B16&C5:C16

    3rd argument Return_array as normal D5:D16

    =XLOOKUP(B2&C2,B5:B16&C5:C16,D5:D16)

    www.eexcel.co.uk 5
    XLOOKUP

    Multiple Criteria using Boolean logic

    You can use the XLOOKUP function and simple Boolean logic
    expressions to return data with multiple criteria.

    1st argument Lookup_value =1

    2nd argument Lookup_array

    (Rage_Name=Name) * (Range_Surname =Surname)

    3rd argument Return_array as normal D5:D16

    =XLOOKUP(1,(B5:B16=B2)*(C5:C16=C2),D5:D16)

    www.eexcel.co.uk 6
    XLOOKUP

    Multiple Criteria for VLOOKUP

    When it comes to others lookup’s functions in Excel, a practical


    solution is to create an additional helper column that concatenate
    the desired criteria.

    =VLOOKUP(B2&C2,A5:D16,4,0)

    www.eexcel.co.uk 7
    XLOOKUP

    TWO-DIMENSIONAL LOOKUP

    2D
    How to create two-dimensional lookup formula?
    #1 Create formula for Vertical Search Value
    =XLOOKUP(C2,B7:B18,C4:H10)
    #2 Create formula for Horizontal Search Value
    =XLOOKUP(C3,C6:E6,C7:E18)
    #3 Combine one formula with another in the 3rd argument.
    =XLOOKUP(C2,B7:B18,XLOOKUP(C3,C6:E6,C7:E18))

    Alternatively, you can use the nested VLOOKUP:

    =VLOOKUP(C2,B7:E18,MATCH(C3,B6:E6,0),0)
    www.eexcel.co.uk 8
    XLOOKUP

    RETURN A RANGE

    Define array formula

    • If you select a range instead of a single cell in the 1st


    argument, you will obtain the relevant correspondence
    result.

    Dynamic arrays are supported in these versions: Excel 365 for


    Windows and Excel 2021.

    www.eexcel.co.uk 9
    XLOOKUP

    • If you select a range of more than one column in the 3rd


    argument, you will return the corresponding result
    accordingly.

    =XLOOKUP(B3,B7:B18,C7:D18)

    Alternatively, you can use:


    =VLOOKUP(B3,B6:E18,{2,3},FALSE)

    www.eexcel.co.uk 10
    XLOOKUP

    CASE-SENSITIVE FORMULA

    A<>a
    In Excel, the general formulas are typically not case-sensitive.
    However, if you encounter a situation where case sensitivity
    matters, fear not! The EXACT function is here to save the day.
    #1 create an EXACT formula that compares two arguments and
    returns TRUE if they are the same, and FALSE if they are not.
    EXACT(B2,$B$5:$B$16)
    An array formula returns a series of TRUE or FALSE
    ={FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;
    FALSE;FALSE}

    #2 you can input this formula as the 2nd argument and use
    TRUE as the 1st argument.
    =XLOOKUP(TRUE,EXACT(B2,$B$5:$B$16),$C$5:$D$16)

    www.eexcel.co.uk 11
    XLOOKUP

    WILDCARD

    ?*~
    Find the last Name 1st from the bottom, starting with the letter
    K and return Name, Surname and Age. If not found, return “Not
    Found”

    =XLOOKUP("K*",B6:B16,B5:D16,"Not Found",2,-1)

    www.eexcel.co.uk 12
    XLOOKUP

    Arguments:
    1. The asterisk (*) represents any numbers of characters after
    letter "K" in this case. Operator (?) represents single
    character.
    2. The range B5:B16 represents the search range where the
    formula will look for the search text.
    3. The range B5:D16 represents the range of fields from which
    the formula will retrieve the corresponding values.
    4. The "Not Found" text will be displayed if no match is found.
    5. The number 2 sets the match mode to allow the use of
    wildcards.
    6. The number -1 specifies that the formula should retrieve
    the last match.
    The results spill to other cells:
    Kristine Garcia 30

    www.eexcel.co.uk 13
    XLOOKUP

    PRO TIPS

    • Compatibility Note: Please be aware that XLOOKUP is only


    available in Excel versions from 2021 onwards.
    • Maintain Correspondence: Ensure that the 2nd and 3rd
    arguments in your XLOOKUP formula correspond correctly.
    Moving the ranges to the wrong positions can lead to
    inaccurate results.
    • Similar like any other Lookups function keep 1st argument
    reference relative but 2nd and 3rd fixed if you want to be
    pasting
    • Alternatively, you can use a range as the first argument to
    receive dynamic results. Therefore, you do not have to fix
    other arguments in this case. However, it is good practice to
    keep them fixed.
    • Returning Multiple Matches: For returning all matches,
    consider using the FILTER function as an effective
    alternative.

    If you are interested in receiving our newsletter, please register


    by clicking the link below.
    https://eexcel.co.uk/Newsletter/

    www.eexcel.co.uk 14
    XLOOKUP

    CONTACT DETAILS:

    Jacek Cetnarski | EEXCEL London


    Data Analyst, VBA Developer, Excel Trainer
    +44 (0)7761 716 681
    [email protected]

    EEXCEL LTD | www.eexcel.co.uk

    • Excel for Beginners


    • Intermediate Excel
    • Advanced Excel

    YouTube Channel

    www.eexcel.co.uk 15

    You might also like