What Is XLOOKUP
Office 365 now boasts a powerful and more useful successor to the VLOOKUP function which is “ XLOOKUP function”.
XLOOKUP is the latest addition in the Excel lookup function family which includes VLOOKUP, HLOOKUP, INDEX+MATCH, LOOKUP and so on. XLOOKUP permits us to search for an item in a given range or table and return matching result. In a manner, it is similar to VLOOKUP, but offers much more than it.
The Excel XLOOKUP function is a newest and flexible replacement for older functions like VLOOKUP, HLOOKUP . XLOOKUP supports approximate and exact matching of data and lookups in vertical or horizontal ranges as well. Like its former lookup functions, XLOOKUP looks for a value in a data table and returns a matching result.
Syntax
=XLOOKUP (lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode])
Arguments
- lookup – The lookup value.
- Lookup array – The array or range to search.
- Return array – The array or range to return.
- Not found – [optional] Value to return if no match is found.
- Match mode – [optional] 0 = exact match (default), -1 = exact match, A value to specify the match mode to use
- Search mode – [optional] 1 = search from first (default), -1 = search from last, A value to specify the search mode to use
What is the difference between XLOOKUP and VLOOKUP?
- XLOOKUP requires you to refer fewer cells. In VLOOKUP an entire data set of a table is referred but this lookup only requires you to refer the relevant columns or rows. This will enable to increase the calculation speed of your spreadsheet and potentially result in fewer errors.
- If you evaluate this with VLOOKUP, then you will see that you no longer need to specify column number or true/false to perform the search.
- It makes the widely used formula dynamic in Excel, straight forward and less error prone. Just write =XLOOKUP (what you want to find, the list, the result list ) and you will get the answer (or #N/A error if the value is not found)
- It looks up the exact match by default. One of the displeasure of VLOOKUP is that you must mention FALSE as last parameter to get correct result. XLOOKUP fixes this by doing exact matches by default. Match mode parameter can be used to change the lookup behavior if needed.
- In various professional situations, you are required to cover the lookup formulas with IFERROR or IFNA formulas to suppress these errors. XLOOKUP offers another parameter where you can instruct the function what default output you want if your value is not found.
- It offers optional parameters to search for unique situations. You can do a search from top or bottom, you can do wild card searches and faster options to search sorted lists.
How to use the XLOOKUP function in Excel with Example!
Example 1: In the below example, XLOOKUP is used to look up a country name in a given range, and then return its telephone country code. The formula comprises of the lookup value (cell F2), lookup array (range B2:B11), and return array (range D2:D11) arguments. It doesn’t include the match mode parameter, as XLOOKUP produces an exact match by default.
Example 2: Suppose you have the following data set and you want to fetch the math score for Greg (lookup value).
Below formula will help you do this:
=XLOOKUP(F2,A2:A15,B2:B15)
XLOOKUP is a power-packed function with many advantages!
Read About H Lookup function in Excel