VLOOKUP, HLOOKUP, and XLOOKUP Formulas Part 3: XLOOKUP Formula
The XLOOKUP function is relatively new, and it was introduced to provide solutions for some of the issues that commonly occur when using the VLOOKUP and HLOOKUP functions. XLOOKUP serves as a sort of replacement for the other two functions mentioned, so if you can master using the XLOOKUP function, you’re all set when it comes to looking up pieces of data in your spreadsheets.
Keep reading to learn more about the XLOOKUP function.
XLOOKUP
The XLOOKUP function differs from both the VLOOKUP function and the HLOOKUP function in that it isn’t dependent on directions. XLOOKUP doesn’t have to search vertically or horizontally to locate and populate a specific piece of information.
The XLOOKUP function looks like this: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Each component of the function has a specific meaning.
- lookup_value: the information you want to locate
- lookup_array: the list you want Excel to search for the information
- return_array: the list you want the results from
- [if_not_found]: the information populated if the value doesn’t exist
- [match_mode]: exact match only setting
- [search_mode]: search from first to last or last to first
It’s worth noting that the XLOOKUP function comes with several optional settings you can include in order to more accurately find the information you’re looking for. Because this function was designed to overcome the setbacks associated with the other two functions, many people prefer to use XLOOKUP to find data in their spreadsheets.
We hope this helps you feel comfortable using the XLOOKUP formula in Excel.
For a detailed demonstration from an expert, with exercises, and the most common questions answered, check out our workshop: Mastering Excel Lookup Functions: What Every User Should Know.
If you’d like to learn additional Excel formulas that can make your job easier, quicker, and more accurate, we’ve got you covered!
Like Learn Excel Now? Follow us on social media and share our content with your networks!
