Today we will talk about the latest member in LOOKUP family and it is rightly being called XLOOKUP because of the X factor it carries. We are already aware of other members of LOOKUP family– VLOOKUP, HLOOKUP, INDEX+MATCH, LOOKUP etc.
XLOOKUP is a super convenient function to search a range (or table) and return matching result. It is very similar to VLOOKUP, but is simpler, easier and more efficient.
XLOOKUP is a modern replacement for the VLOOKUP function. It was introduced in MS Office 365 with the aim of eliminating the problems that users faced while using the VLOOKUP function.
If you don’t have offline access to MS Office 365, you can use the XLOOKUP function online by signing into Office Online through the Microsoft website.
Here is a video you can watch to understand XLOOKUP
What makes XLOOKUP good enough to almost replace all other LOOKUP functions? First of all XLOOKUP makes the most used formula in Excel simpler and less prone to errors. You just need to give three inputs =XLOOKUP (what you want to find, the list, the result list) and that is all, you get the desired output (or #N/A error if the value is not found).
The first input in the formula is the name for whom you want to find the corresponding value. It is called the “lookup value” and is similar to VLOOKUP function.
For Example –
In the table given alongside you are required to find the bonus of each employee working in the organization. Hence the first input in the XLOOKUP formula would be the “First Name” of the employee.
If calculated for Daoud, cell B2 will be selected as the lookup value.
The second input in the formula is the column that contains the list of all lookup values. It is called the “lookup array” and is different from VLOOKUP as the need to select the entire table is no longer there.
For Example –
The entire “First Name” column will be selected as the second input.
The third input in the formula is the column that contains the result values corresponding to the lookup value. It is called “return array”. Unlike VLOOKUP, XLOOKUP is unaffected by whether the return array in the table is right to the lookup array or not.
For Example –
The entire “Bonus %” column will be selected as the third input.
Remember to freeze the second and third input columns by pressing Function F4. This is necessary in both VLOOKUP as well as XLOOKUP.
The fourth parameter for exact match and approximate match has been done away with and exact match result have been made default. This makes it simpler and convenient.
An added advantage of XLOOKUP over VLOOKUP is that to handle errors fourth parameter if value not found is introduced, which helps us to remove errors in one step. If a particular lookup value is not present in the lookup array, the result can be customized as per the user which is not possible in VLOOKUP where the result will always be #N/A error.
To customize the result, you simply have to put your desired result as the fourth input in the formula and it will be shown instead of #N/A error.
XLOOKUP offers optional parameters to search for special situations. You can search from top or bottom, you can do wild card searches and faster options to search sorted lists.
XLOOKUP offers several important advantages, especially compared to VLOOKUP:
XLOOKUP can lookup data to the right or left of lookup values
XLOOKUP can return multiple results (example #3 above)
XLOOKUP defaults to an exact match (VLOOKUP defaults to approximate)
XLOOKUP can work with vertical and horizontal data
XLOOKUP can perform a reverse search (last to first)
XLOOKUP can return entire rows or columns, not just one value
XLOOKUP can work with arrays natively to apply complex criteria
Watch this video for absolute clarity of the topic