Yes, its true, It is time to forget Vlookup, Hlookup, Index-Match as Microsoft Excel is now fully loaded with XLOOKUP. This best in breed function is called XLOOKUP as it can do Cross lookup (*vertically as well as horizontally). Here is the quick syntax:
=XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Where:
- lookup_value – value to find (same as VLOOKUP/HLOOKUP)
- lookup_array – lookup range (can be Vertical or Horizontal, the lookup column can be anywhere in the table)
- return_array – return range (can be Vertical or Horizontal)
- [if_not_found] – Optional – Where a valid match is not found, return the [if_not_found] text you supply. Skipping this optional parameter will result in usual #N/A error as in Vlookups.
- [match_mode] – Optional – 0 for exact match by default + more options as shown below:
- [search_mode] – Optional– 1 for first-to-last + more options as shown below:
Now I know that you are excited and thrilled to learn this new and powerful formula but at the same time feel overwhelmed with the options and parameters. No doubt XLOOKUP will be one of the hottest and toughest question for interviews. Relax……
Here is an Excel file which can help you to quickly adapt to this new formula with real world questions and scenarios and resolving any doubts with Match_mode and Search_mode.
Feeling better…Please feel free to Like, Share and comment this article to spread the knowledge to everyone.
Follow me on Linkedin, Medium, GitHub for more stuff like this
