XLOOKUP: With the help of XLOOKUP function you can find or extract data from table or range.
What is XLOOKUP Function?
The XLOOKUP function is a powerful Excel function that allows you to find specific data in a range, retrieve corresponding values, and perform advanced lookups. It is an improved alternative to the VLOOKUP and HLOOKUP functions.
With the XLOOKUP function, you can search for data horizontally or vertically, specify search modes (exact match or approximate match), and handle situations where a value is not found. It supports multiple lookup criteria and can return values from different columns.
The XLOOKUP function has a more intuitive syntax and provides more flexibility compared to its predecessors. It is commonly used in various scenarios, such as retrieving prices from a product list, searching for customer details based on their IDs, or extracting data from complex tables.
Overall, the XLOOKUP function is a valuable tool for data analysis and lookup operations in Excel.
Syntax of XLOOKUP
The syntax of the XLOOKUP function in Excel is as follows:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Here’s a breakdown of each parameter:
lookup_value
: The value you want to find in the lookup array.lookup_array
: The range or array where you want to search for the lookup value.return_array
: The range or array from which you want to return a value.[if_not_found]
(optional): The value to return if the lookup value is not found. By default, it is set to#N/A
.[match_mode]
(optional): Specifies how the function should handle approximate matches. Options include0
(exact match),1
(exact match or next smaller item),-1
(exact match or next larger item). By default, it is set to1
.[search_mode]
(optional): Specifies the direction of the search. Options include1
(search from top to bottom) or-1
(search from bottom to top). By default, it is set to1
.
Learn Also | VLOOKUP Function : Excel Practice Online for free 2024
The XLOOKUP function is quite flexible and allows you to perform various types of lookups based on your requirements.
Example
Suppose we have below given data from that data we want to extract Name from Rank
Rank | Name | Total net worth | Country/Region |
---|---|---|---|
1 | Elon Musk | 222 | United States |
2 | Jeff Bezos | 171 | United States |
3 | Bernard Arnault | 168 | France |
4 | Bill Gates | 135 | United States |
5 | Larry Ellison | 131 | United States |
6 | Steve Ballmer | 129 | United States |
=XLOOKUP(F5,A4:A104,B4:B104)
Practice XLOOKUP Now
Here you can practice XLOOKUP just start type = and so practice now
Join EduTaxTuber Network for the Latest News and updates on Income Tax, GST, Company Law, Stock Market and other related subjects.
Disclaimer: The views expressed are solely those of the author and Edutaxtuber. The content in this piece is solely intended for informational purposes and for personal, non-commercial use. It should not be considered as professional advice or an endorsement by any organization. The author, the organization, and its affiliates disclaim any liability for any loss or harm resulting from the information in this article, as well as for any decisions made based on it.