XLOOKUP: The Ultimate Lookup Function for Excel 2019 - Download Now

alciopatingrigamip
Aug 6, 2023
14 min read

How to Download XLOOKUP for Excel 2019 64 Bit

If you are an Excel user, you probably know how useful the VLOOKUP function is for finding and retrieving data from a table or range. However, you may also be aware of its limitations, such as requiring the lookup column to be the first one, returning only one value per lookup, and not handling errors or missing values well.

That's why Microsoft introduced a new function called XLOOKUP in August 2019, which is designed to replace VLOOKUP and other lookup functions. XLOOKUP is more powerful, flexible, and user-friendly than VLOOKUP, and it can handle many scenarios that VLOOKUP cannot.

download xlookup for excel 2019 64 bit

Download Zip

However, there is a catch: XLOOKUP is only available in Excel in Microsoft 365, which is the subscription-based version of Office. If you are using Excel 2019, which is the standalone version of Office, you won't be able to use XLOOKUP unless you upgrade to Microsoft 365.

But don't worry, there is a way to get XLOOKUP for Excel 2019 64 Bit without upgrading. In this article, we will show you how to download and use a custom function add-in that mimics the behavior of XLOOKUP. We will also explain how to use XLOOKUP in Excel 2019 64 Bit, and share some best practices and tips for using it effectively.

What is XLOOKUP and why you need it

XLOOKUP is a new function in Excel that allows you to look up a value in a range or array, and return a corresponding value from another range or array. It has the following advantages over VLOOKUP:

It can look up values in any column, not just the first one.

It can return multiple values from the same row or column.

It can perform exact, approximate, or wildcard matches.

It can search from the first or last item, or perform a binary search.

It can handle errors and missing values gracefully.

You can use XLOOKUP for various purposes, such as:

Finding and retrieving data from tables or ranges.

Performing calculations based on lookup values.

Creating dynamic references to cells or ranges.

Comparing and validating data across sources.

Extracting and manipulating text or numbers.

The benefits of using XLOOKUP over VLOOKUP

To illustrate the benefits of using XLOOKUP over VLOOKUP, let's look at an example. Suppose you have a table of employee data like this:

IDNameDepartmentSalary

E001AliceSales$5,000

E002BobMarketing$4,500

E003CharlieFinance$6,000

E004DaveIT$5,500

E005EveHR$4,000

If you want to use VLOOKUP to find the salary of Bob, you would have to use a formula like this:

How to install xlookup add-in for excel 2019 64 bit

Xlookup function not working in excel 2019 64 bit

Xlookup add-in for excel 2013, 2016 and 2019 download

Excelkid xlookup add-in for excel 2019 64 bit

Xlookup vs vlookup in excel 2019 64 bit

Xlookup function examples for excel 2019 64 bit

Xlookup syntax and arguments in excel 2019 64 bit

Xlookup with multiple criteria in excel 2019 64 bit

Xlookup with iferror in excel 2019 64 bit

Xlookup with wildcards in excel 2019 64 bit

Xlookup with approximate match in excel 2019 64 bit

Xlookup with reverse search in excel 2019 64 bit

Xlookup with binary search in excel 2019 64 bit

Xlookup with array formulas in excel 2019 64 bit

Xlookup with dynamic arrays in excel 2019 64 bit

Xlookup with data validation in excel 2019 64 bit

Xlookup with conditional formatting in excel 2019 64 bit

Xlookup with pivot tables in excel 2019 64 bit

Xlookup with power query in excel 2019 64 bit

Xlookup with power pivot in excel 2019 64 bit

Xlookup with power bi in excel 2019 64 bit

Xlookup with slicers in excel 2019 64 bit

Xlookup with charts and graphs in excel 2019 64 bit

Xlookup with sparklines in excel 2019 64 bit

Xlookup with data tables in excel 2019 64 bit

Xlookup with scenarios in excel 2019 64 bit

Xlookup with solver in excel 2019 64 bit

Xlookup with goal seek in excel 2019 64 bit

Xlookup with what-if analysis in excel 2019 64 bit

Xlookup with macros and vba in excel 2019 64 bit

Xlookup with user-defined functions in excel 2019 64 bit

Xlookup with custom number formats in excel 2019

=VLOOKUP("Bob",A2:D6,4,FALSE)

This formula works, but it has some drawbacks:

You have to specify the entire table range (A2:D6) as the lookup array, which can be cumbersome and prone to errors.

You have to use the column index number (4) to indicate which column to return, which can be confusing and hard to maintain.

You have to use the exact match mode (FALSE) to avoid getting incorrect results, which can be slow and inefficient.

You cannot look up values in columns to the left of the lookup column (Name), such as ID or Department.

You cannot return multiple values from the same row, such as Name and Department.

You cannot handle errors or missing values in the lookup or return arrays, such as #N/A or blank cells.

Now, let's see how you can use XLOOKUP to achieve the same result:

=XLOOKUP("Bob",B2:B6,D2:D6)

This formula is much simpler and better than VLOOKUP:

You only have to specify the lookup column (B2:B6) and the return column (D2:D6) as the lookup and return arrays, which makes it easier and more accurate.

You don't have to use any column index numbers, which makes it clearer and more flexible.

You don't have to specify the match mode, as XLOOKUP defaults to exact match mode, which makes it faster and more reliable.

You can look up values in any column, not just the first one, which makes it more versatile.

You can return multiple values from the same row by using an array as the return array, such as B2:C6, which makes it more powerful.

You can handle errors or missing values in the lookup or return arrays by using the optional arguments of XLOOKUP, such as if_not_found or if_error, which makes it more robust.

The limitations of XLOOKUP and how to overcome them

As you can see, XLOOKUP is a great improvement over VLOOKUP, but it is not perfect. It still has some limitations that you should be aware of, such as:

It is only available in Excel in Microsoft 365, which means you cannot use it in Excel 2019 or earlier versions.

It is not compatible with some older functions or features in Excel, such as Data Validation or Conditional Formatting.

It may not work well with some complex or dynamic data types or structures, such as arrays, tables, or spilled ranges.

Fortunately, there are ways to overcome these limitations and use XLOOKUP in Excel 2019 64 Bit. In the next section, we will show you how to get XLOOKUP for Excel 2019 64 Bit by using a custom function add-in that simulates the behavior of XLOOKUP.

How to get XLOOKUP for Excel 2019 64 Bit

If you want to use XLOOKUP in Excel 2019 64 Bit, you have two options:

The official way: Upgrade to Microsoft 365 and get access to all the latest features and updates in Excel, including XLOOKUP.

The alternative way: Use a custom function add-in that mimics the behavior of XLOOKUP and works in Excel 2019 64 Bit.

The official way: Upgrade to Microsoft 365

The official way to get XLOOKUP for Excel 2019 64 Bit is to upgrade to Microsoft 365, which is the subscription-based version of Office. Microsoft 365 offers many benefits over Excel 2019, such as:

Access to all the latest features and updates in Excel, including XLOOKUP and other new functions like FILTER, SORT, UNIQUE, etc.

Access to cloud-based services and apps like OneDrive, SharePoint, Teams, Power BI, etc.

Access to premium templates and content like stock images, icons, fonts, etc.

Access to advanced security and support features like ransomware protection, password recovery, advanced threat protection, etc.

Access to multiple devices and platforms like Windows, Mac, iOS, Android, etc.

If you are interested in upgrading to Microsoft 365, you can choose from different plans and pricing options, depending on your needs and preferences. You can also try Microsoft 365 for free for one month before you decide to buy it.

To upgrade to Microsoft 365, you can follow these steps:

Go to the and choose the plan that suits you best.

Click on the Buy now or Try for free button and sign in with your Microsoft account or create a new one.

Enter your payment details and confirm your order or start your free trial.

Download and install Microsoft 365 on your device and activate it with your account.

Open Excel and enjoy using XLOOKUP and other new features.

The alternative way: Use a custom function add-in

If you don't want to upgrade to Microsoft 365, but still want to use XLOOKUP in Excel 2019 64 Bit, you can use a custom function add-in that mimics the behavior of XLOOKUP. A custom function add-in is a file that contains user-defined functions that you can use in Excel, just like built-in functions.

One of the custom function add-ins that you can use to get XLOOKUP for Excel 2019 64 Bit is the by Excel Campus. This add-in contains several custom functions that emulate the new functions in Excel in Microsoft 365, including XLOOKUP. The custom function for XLOOKUP is called X_vLOOKUP, and it has the same syntax and arguments as XLOOKUP.

How to install and use the Custom Functions add-in

To install and use the Custom Functions add-in by Excel Campus, you can follow these steps:

Go to the and enter your name and email address to get the download link.

Download the Custom Functions add-in file (CustomFunctions.xlam) and save it in a trusted location on your device.

Open Excel and go to File > Options > Add-ins.

In the Manage drop-down list, select Excel Add-ins and click Go.

In the Add-Ins dialog box, click Browse and locate the Custom Functions add-in file that you downloaded.

Select the Custom Functions add-in file and click OK.

You should see a message that says "The Custom Functions add-in is ready to use". Click OK.

You should also see a new tab called Custom Functions in the ribbon, where you can access the custom functions in the add-in.

How to use the X_vLOOKUP function in the add-in

To use the X_vLOOKUP function in the Custom Functions add-in, you can follow these steps:

Select a cell where you want to enter the formula.

Go to the Custom Functions tab in the ribbon and click on X_vLOOKUP.

In the Function Arguments dialog box, enter or select the arguments for the function, such as lookup_value, lookup_array, return_array, etc.

Click OK to enter the formula in the cell.

You should see the result of the formula in the cell, just like using XLOOKUP.

Note that the X_vLOOKUP function in the Custom Functions add-in is not exactly identical to XLOOKUP in Microsoft 365. It may have some differences or limitations in terms of performance, compatibility, or functionality. For more information about the Custom Functions add-in and its features, you can visit .

How to use XLOOKUP in Excel 2019 64 Bit

Whether you use XLOOKUP in Microsoft 365 or X_vLOOKUP in the Custom Functions add-in, you can use them in Excel 2019 64 Bit in a similar way. In this section, we will explain how to use XLOOKUP in Excel 2019 64 Bit, and share some best practices and tips for using it effectively.

The syntax and arguments of XLOOKUP

The syntax of XLOOKUP is as follows:

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

The arguments of XLOOKUP are as follows:

lookup_value: The value that you want to look up in the lookup_array. It can be a number, text, logical value, cell reference, or named range.

lookup_array: The range or array that contains the lookup values. It can be a single row, single column, or multiple rows and columns.

return_array: The range or array that contains the return values. It must have the same size and shape as the lookup_array.

[if_not_found]: Optional. The value that you want to return if the lookup_value is not found in the lookup_array. It can be a number, text, logical value, cell reference, or named range. If omitted, it defaults to #N/A.

[match_mode]: Optional. The mode that you want to use to match the lookup_value with the lookup_array. It can be one of the following values:

0 or omitted: Exact match. The function will look for an exact match of the lookup_value in the lookup_array. If there are multiple matches, it will return the first one.

-1: Exact match or next smaller item. The function will look for an exact match of the lookup_value in the lookup_array. If there is no exact match, it will return the next smaller item. The lookup_array must be sorted in ascending order.

1: Exact match or next larger item. The function will look for an exact match of the lookup_value in the lookup_array. If there is no exact match, it will return the next larger item. The lookup_array must be sorted in descending order.

2: Wildcard match. The function will look for a partial match of the lookup_value in the lookup_array, using the wildcard characters * (asterisk) and ? (question mark). The asterisk matches any sequence of characters, and the question mark matches any single character.

[search_mode]: Optional. The mode that you want to use to search for the lookup_value in the lookup_array. It can be one of the following values:

1 or omitted: Search from first to last item. The function will start searching from the first item in the lookup_array and move towards the last item.

-1: Search from last to first item. The function will start searching from the last item in the lookup_array and move towards the first item.

2: Binary search (ascending order). The function will use a binary search algorithm to find the lookup_value in the lookup_array. The lookup_array must be sorted in ascending order.

-2: Binary search (descending order). The function will use a binary search algorithm to find the lookup_value in the lookup_array. The lookup_array must be sorted in descending order.

[if_error]: Optional. The value that you want to return if an error occurs during the calculation of the function. It can be a number, text, logical value, cell reference, or named range. If omitted, it defaults to #N/A.

Examples of using XLOOKUP with different arguments

To demonstrate how to use XLOOKUP with different arguments, let's use the same table of employee data as before:

IDNameDepartmentSalary

E001AliceSales$5,000

E002BobMarketing$4,500

E003CharlieFinance$6,000

E004DaveIT$5,500

E005EveHR$4,000

Here are some examples of using XLOOKUP with different arguments:

To find the salary of Bob using exact match mode and search from first to last item, you can use this formula:

=XLOOKUP("Bob",B2:B6,D2:D6)

To find the department of Eve using exact match mode and search from last to first item, you can use this formula:

=XLOOKUP("Eve",B2:B6,C2:C6,,,-1)

To find the ID of the employee whose salary is closest to $5,000 using exact match or next smaller item mode and binary search (ascending order), you can use this formula:

=XLOOKUP(5000,D2:D6,A2:A6,,,-1,2)

To find the name and department of the employee whose ID is E003 using exact match mode and search from first to last item, and return an array of values, you can use this formula:

=XLOOKUP("E003",A2:A6,B2:C6)

To find the salary of the employee whose name starts with "A" using wildcard match mode and search from first to last item, you can use this formula:

=XLOOKUP("A*",B2:B6,D2:D6)

To find the department of the employee whose ID is E006 using exact match mode and search from first to last item, and return "Not found" if the lookup value is not found, you can use this formula:

=XLOOKUP("E006",A2:A6,C2:C6,"Not found")

To find the name of the employee whose salary is $4,500 using exact match mode and search from first to last item, and return "Error" if an error occurs during the calculation, you can use this formula:

=XLOOKUP(4500,D2:D6,B2:B6,,0,,,"Error")

The best practices and tips for using XLOOKUP

Now that you know how to use XLOOKUP in Excel 2019 64 Bit, here are some best practices and tips for using it effectively:

Use descriptive names or references for your lookup and return arrays, to make your formulas easier to read and maintain.

Use the appropriate match mode and search mode for your lookup scenario, to get the correct results and improve the performance of your formulas.

Use the if_not_found and if_error arguments to handle errors and missing values in your lookup or return arrays, to avoid unwanted results or messages.

Use an array as the return array to return multiple values from the same row or column, to expand your lookup capabilities.

Use XLOOKUP with other functions or features in Excel, such as SUMIFS, INDEX, MATCH, FILTER, SORT, UNIQUE, etc., to perform advanced lookups and calculations.

How to handle errors and missing values with XLOOKUP

One of the common challenges when using lookup functions in Excel is how to handle errors and missing values in your lookup or return arrays. For example, you may encounter #N/A errors if the lookup value is not found, #REF errors if the lookup or return array is invalid, #VALUE errors if the lookup value or return value is not compatible with the expected data type, or blank cells if the lookup or return array contains empty values.

XLOOKUP provides two optional arguments that can help you handle these situations: if_not_found and if_error. The if_not_found argument allows you to specify what value to return if the lookup value is not found in the lookup array. The if_error argument allows you to specify what value to return if an error occurs during the calculation of the function. You can use any number, text, logical value, cell reference, or named range as the value for these arguments.

For example, suppose you have a table of product data like this:

Product IDProduct NamePriceQuantity

P001Laptop$1,00010

P002Tablet$50020

P003Smartphone$80015

P004Smartwatch$30025

P005Headphones$10030

If you want to use XLOOKUP to find the price of the product with ID P006, you would get a #N/A error, because there is no such product in the table. To avoid this error, you can use the if_not_found argument to return a custom message, such as "Product not found". For example:

=XLOOKUP("P006",A2:A6,C2:C6,"Product not found")

If you want to use XLOOKUP to find the quantity of the product with name Tablet, you would get a blank cell, because there is an empty cell in the lookup array. To avoid this blank cell, you can use the if_error argument to return a default value, such as 0. For example:

=XLOOKUP("Tablet",B2:B6,D2:D6,,0,,,"0")

How to perform advanced lookups with XLOOKUP

Another advantage of using XLOOKUP is that you can perform advanced lookups that are not possible or easy with VLOOKUP or other lookup functions. For example, you can use XLOOKUP to:

Perform two-way lookups by using XLOOKUP inside another XLOOKUP.

Perform case-sensitive lookups by using the EXACT function inside XLOOKUP.

Perform multiple criteria lookups by using the FILTER function inside XLOOKUP.

Perform reverse lookups by using the -1 or -2 values for the search_mode argument.

Perform fuzzy lookups by using the Fuzzy Lookup add-in or the FUZZYMATCH function inside XLOOKUP.

To demonstrate how to perform advanced lookups with XLOOKUP, let's use the same table of product data as before:

Product IDProduct NamePriceQuantity

P001Laptop$1,00010

P002Tablet$50020

P003Smartphone$80015

P004Smartwatch$30025

P005Headphones$10030

Here are some examples of performing advanced lookups with XLOOKUP:

To perform a two-way lookup and find the price of the product with ID P003 and name Smartphone, you can use this formula:

=XLOOKUP("P003",A2:A6,XLOOKUP("Smartphone",B2:B6,C2:C6))

To perform a case-sensitive lookup and find the quantity of the product with name Tablet (with a capital T), you can use this formula:

=XLOOKUP(TRUE,EXACT("Tablet",B2:B6),D2:D6)

To perform a multiple criteria lookup and find the product ID of the product with price $800 and quantity 15, you can use this formula:

=XLOOKUP(1,FILTER(A2:A6,(C2:C6=800)*(D2:D6=15)),A2:A6)

To perform a reverse lookup and find the product name of the product with ID P005, you can use this formula:

=XLOOKUP("P005",A2:A6,B2:B6,,,-1)

To perform a fuzzy lookup and find the product name of the product that is most similar to "Lap Top", you can use this formula (after installing the Fuzzy Lookup add-in or using the FUZZYMATCH function):

=XLOOKUP(MAX(FUZZYMATCH("Lap Top",B2:B6)),FUZZYMATCH("Lap Top",B2:B6),B2:B6)

Conclusion and FAQs

In this article, we have shown you how to download XLOOKUP for Excel 2019 64 Bit, either by upgrading to Microsoft 365 or by using a custom function add-in. We have also explained how to use XLOOKUP in Excel 2019 64 Bit, and shared some best practices and tips for using it effectively.

XLOOKUP is a powerful and flexible function that can help you perform various lookup tasks in Excel. It can replace VLOOKUP and other lookup functions, and handle many scenarios that they cannot. It can also work well with other functions or features in Excel, and perform advanced lookups that are not possible or easy with other functions.

If you want to learn more about XLOOKUP and how to use it in Excel, you can check out these resources:

We hope you have found this article helpful and informative. If you have any questions or feedback, please feel free to leave a comment below. Here are some FAQs that may answer some of your queries:

FAQs

Q: What is the difference between XLOOKUP and VLOOKUP?

A: XLOOKUP is a new function in Excel that allows you to look up a value in a range or array, and return a corresponding value from another range or array. It has many advantages over VLOOKUP, such as being able to look up values in any column, return multiple values from the same row or column, perform exact, approximate, or wildcard matches, search from the first or last item, or perform a binary search, and handle errors and missing values gracefully.

Q: How do I get XLOOKUP for Excel 2019 64 Bit?

A: You can get XLOOKUP for Excel 2019 64 Bit by either upgrading to Microsoft 365, which is the subscription-based version of Office that includes all the latest features and updates in Excel, including XLOOKUP, or by using a custom function add-in that mimics the behavior of XLOOKUP and works in Excel 2019 64 Bit.

Q: How do I use XLOOKUP in Excel 2019 64 Bit?

A: You can use XLOOKUP in Excel 2019 64 Bit by entering the following syntax in a cell:

=XLOOKUP(lookup_value,lookup _array,return_array,[if_not_found],[match_mode],[search_mode],[if_error])

You can replace the arguments with the values that you want to use for your lookup scenario. For example, to find the price of the product with ID P003, you can use this formula:

=XLOOKUP("P003",A2:A6,C2:C6)

You can also use the Custom Functions tab in the ribbon to insert the XLOOKUP function and enter the arguments in the Function Arguments dialog box.

Q: What are some best practices and tips for using XLOOKUP?

A: Some best practices and tips for using XLOOKUP are:

Use descriptive names or references for your lookup and return arrays, to make your formulas easier to read and maintain.

Use the appropriate match mode and search mode for your lookup scenario, to get the correct results and improve the performance of your formulas.

Use the if_not_found and if_error arguments to handle errors and missing values in your lookup or return arrays, to avoid unwanted results or messages.

Use an array as the return array to return multiple values from the same row or column, to expand your lookup capabilities.

Use XLOOKUP with other functions or features in Excel, such as SUMIFS, INDEX, MATCH, FILTER, SORT, UNIQUE, etc., to perform advanced lookups and calculations.

Q: How do I perform advanced lookups with XLOOKUP?

A: You can perform advanced lookups with XLOOKUP by using it with other functions or features in Excel, such as:

Perform two-way lookups by using XLOOKUP inside another XLOOKUP.

Perform case-sensitive lookups by using the EXACT function inside XLOOKUP.

Perform multiple criteria lookups by using the FILTER function inside XLOOKUP.

Perform reverse lookups by using the -1 or -2 values for the search_mode argument.

Perform fuzzy lookups by using the Fuzzy Lookup add-in or the FUZZYMATCH function inside XLOOKUP.

44f88ac181

Modern Dance Studio