In this Microsoft Excel blog, we will learn how to use XLOOKUP to search for a specific value in a range of cells and retrieve a corresponding value from another range of cells. We will show you today
Basic Syntax And 3 Examples
1.
Example 1: Finding the Email Address of a Person Based on their Name
2.
Example 2: Finding Sales Figures for a Specific Product in a Specific Month
3.
Example 3: Finding the Stock Price for a Specific Company on a Specific Day
Basic Syntax
The basic syntax of XLOOKUP is as follows: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode])
Here is a brief explanation of each of the parameters:
Example 1 - Finding the Email Address of a Person Based on their Name Let's say we have two tables, Table1 and Table2. Table1 contains a list of names, and Table2 contains a list of names and their corresponding email addresses. We can use XLOOKUP to find the email address of a person whose name is in cell A2 with the following formula: =XLOOKUP(A2, Table2[Name], Table2[Email], "", 0)
Example 2 - Finding Sales Figures for a Specific Product in a Specific Month Suppose we have a table named "Sales Data," which contains sales figures for different products and months. We can use XLOOKUP to find the sales figures for a specific product in a specific month with the following formula: =XLOOKUP(A2, SalesData[[Product value]:[Product value]], XLOOKUP (B2, SalesData[[Jan]:[Dec]], SalesData[[Jan Value]:[Dec Value]],))
Example 3 - Finding the Stock Price for a Specific Company on a Specific Day Suppose we have a table named "Stock Data," which contains stock prices for different companies and different days. We can use XLOOKUP to find the stock price for a specific company on a specific day with the following formula: =XLOOKUP(A2&"-"&B2, StockData[Date&"-"&Company], StockData[Price], "", 1)
Section 5: Conclusion By using XLOOKUP, we can easily retrieve information from large data sets and simplify our data analysis tasks in Microsoft Excel. We hope you found this tutorial helpful. Thank you for watching, and don't forget to subscribe for more Excel tips and tutorials!
Section 6: Example Tables Let's take a look at the example tables that we will be using in this tutorial.
Table 1: Names
|
A |
B |
|
|
1 |
Name |
|
|
2 |
John |
|
|
3 |
Jane |
|
|
4 |
Peter |
|
|
5 |
Sarah |
Table 2: Names and Email Addresses
|
A |
B |
|
|
1 |
Name |
|
|
2 |
John |
|
|
3 |
Jane |
|
|
4 |
Peter |
|
|
5 |
Sarah |
Table 3: Sales Data
|
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
L |
|
|
1 |
Product |
Jan |
Feb |
Mar |
Apr |
May |
Jun |
Jul |
Aug |
Sep |
Oct |
Nov |
|
2 |
Product A |
$5,000 |
$5,500 |
$6,000 |
$5,800 |
$5,700 |
$6,200 |
$5,900 |
$6,100 |
$5,400 |
$5,800 |
$5,700 |
|
3 |
Product B |
$3,500 |
$3,800 |
$4,200 |
$4,100 |
$4,300 |
$4,600 |
$4,500 |
$4,700 |
$4,400 |
$4,100 |
$4,300 |
|
4 |
Product C |
$6,200 |
$6,800 |
$6,400 |
$6,700 |
$6,500 |
$7,000 |
$6,800 |
$7,100 |
$6,300 |
$6,900 |
$6,500 |
Table 4: Stock Data
|
A |
B |
C |
|
|
1 |
Date |
Company |
Price |
|
2 |
1/1/2023 |
Apple Inc. |
$150.2 |
|
3 |
1/1/2023 |
Google LLC |
$275.1 |
|
4 |
1/2/2023 |
Apple Inc. |
$153.5 |
|
5 |
1/2/2023 |
Google LLC |
$277.8 |
|
6 |
1/3/2023 |
Apple Inc. |
$151.8 |
|
7 |
1/3/2023 |
Google LLC |
$271.5 |
|
8 |
1/4/2023 |
Apple Inc. |
$155.2 |
|
9 |
1/4/2023 |
Google LLC |
$273.4 |
Section 7: Conclusion XLOOKUP is a powerful tool in Microsoft Excel that allows us to search for a specific value and retrieve a corresponding value from a table. It's a versatile function that can be used in a wide range of scenarios, from simple lookups to more complex searches with multiple criteria.
In this tutorial, we covered the basic syntax of the XLOOKUP function, including how to specify the lookup value, the lookup array, and the return array. We also explored some of the optional arguments, such as the match mode and the return mode.
We then went on to demonstrate several practical examples of using XLOOKUP in real-world scenarios. We looked at how to use XLOOKUP to:
By following these examples and experimenting with XLOOKUP in your own work, you'll be able to unlock the full potential of this powerful Excel function.
Thank you for watching this tutorial on Microsoft Excel XLOOKUP. If you have any questions or comments, please leave them below. Don't forget to subscribe to our channel for more Excel tutorials and tips.
We have to make sure that we have to fix the current range. We don’t want our array to shift while we pull the formula down. So let’s highlight this and press f4 to fix everything. Again, we have to fix the range, press f4 to fix.
Notice that the other arguments here are optional. We don’t need them right now. We’re going to take a look at them in a second. If the default not found parameter is not provided, the function will return the #N/A error value if the lookup value is not found. And the default match_mode for xlookup is to look for an exact match. So, we don’t need to specify anything else right now. We can just press enter and we’re done. Since we have done the fixing correctly here.
XLOOKUP function can combine two lookup values and two lookup arrays to find a matching value in a specified range. The ampersand (&) operator is used to concatenate the two values into a single lookup value. Again, the ampersand operator is used to concatenate the two ranges into a single lookup array.
lookup_value1&lookup_value2 lookup_array1&lookup_array2
By combining two lookup values and two lookup arrays, the XLOOKUP function allows you to perform more complex searches in Excel.
Hello and welcome to this video tutorial on Microsoft Excel's XLOOKUP function.
Xlookup is not available in all excel versions. This function in Microsoft Excel was introduced in Excel 365 and Excel 2019 or later versions. So, if you are using earlier versions of Excel 2016, 2013 or 2010, you would not have access to xlookup.
Xlookup can simplify many common tasks such as vlookup, hlookup, and index/match functions.
Three optional options, if not found, Match Mode and Search Mode.
The basic syntax for the XLOOKUP function in Microsoft Excel is as follows:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Let's go through each argument in detail:
lookup_value: This is the value that you want to search for in the lookup_array. It can be a cell reference, a text string, or a numerical value.
lookup_array: This is the range of cells that you want to search in. It can be a column or a row of data, or a range of cells that contains your lookup values.
return_array: This is the range of cells that contains the values that you want to return when a match is found. It can be a column or a row of data, or a range of cells that contains your return values.
if_not_found (optional): This is the value that you want to return if no match is found. It can be any value or text string that you want to display, or you can leave it blank to display the #N/A error.
match_mode (optional): This parameter specifies whether you want to search for an exact match or an approximate match. If you leave this argument blank, XLOOKUP will default to an exact match.
search_mode (optional): This parameter specifies the direction in which you want to search for a match. You can search from the beginning of the lookup_array to the end, or from the end to the beginning. If you leave this argument blank, XLOOKUP will default to searching from the beginning of the lookup_array to the end.
I won’t discuss and use today all optional options. In another video I will show and explain Xlookup all optional options with examples.
In this Microsoft Excel blog, we will learn how to use XLOOKUP to search for a specific value in a range of cells and retrieve a corresponding value from another range of cells. We will show you today
Read more