XLOOKUP is like a super-powered search function in Excel. It's overcome the limitations of VLOOKUP and has a ton of flexibility. Let's learn more about XLOOKUP and how to use it.
XLOOKUP is like a super-powered search function in Excel. Imagine you have a big list of names and phone numbers, but you only want to find one specific phone number. You could use XLOOKUP to search through the list and find that phone number quickly.
Here's how it works: You tell XLOOKUP what you're looking for (like a name or a number), and where to look for it (like a table or range of cells). Then, XLOOKUP goes through that list and finds the exact match you're looking for.
It's a really useful tool for working with big sets of data in Excel, because it can help you find the information you need much faster than manually searching through a list.
XLOOKUP vs VLOOKUP
XLOOKUP is a newer function in Excel that was introduced in 2019, while VLOOKUP has been around for a long time. Both functions allow you to search for data in a table and return related information, but there are some key differences between them.
The main advantage of XLOOKUP over VLOOKUP is that it is more flexible and powerful. Here are some of the key differences:
Syntax of XLOOKUP
Let’s see what each argument means:
lookup_value: refers to the value that you want to search for in a range or array
lookup_array: refers to the range or array where you want to search for the lookup_value
return_array: refers to the range or array that contains the values that you want to return
[[if_not_found]: is an optional parameter that specifies what should be returned if XLOOKUP cannot find a match for the lookup_value in the lookup_array
[search_mode]: is an optional parameter that specifies how XLOOKUP should perform the search for the lookup_value in the lookup_array
[match_mode]: is an optional parameter that specifies how XLOOKUP should match the lookup_value with the values in the lookup_array
Let’s understand XLOOKUP with an example.
Let’s say, we have to find the employee with the employee ID 5124 from the following table. In this case, VLOOKUP doesn’t work as the Lookup Array is on the right and VLOOKUP expects the Lookup Array to be on the left. Let’s see how we can use XLOOKUP here.
Here, you need to use XLOOKUP, provide column C as the lookup_array and column A as the return_array. If you are doing it on the third sheet while the table is on the first sheet named ‘Data’, the formula would look like it’s there in the following screenshot and you will get the name of the employee.
This is one practical use case of XLOOKUP. If you want to learn about the other arguments of XLOOKUP Syntax and learn XLOOUP in-detail with different examples, the following video is for you.
Join over 50,000+ brands already growing with Winuall.