It’s time to settle one of the most heated debates in Excel: VLOOKUP, INDEX MATCH, or XLOOKUP? Well friends, I’m officially Team XLOOKUP ✖. Sorry to all my die-hard Index Match lovers 💔 and for those of you still using VLOOKUP...I’m here to help 😵💫. Microsoft addressed many of VLOOKUP’s limitations with the release of their new and improved version, XLOOKUP, which will look up a value in an array and return a corresponding value from another array.
Follow the function syntax below:
=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode)
This XLOOKUP function searches for the “lookup_value” in the “lookup_array” and returns the corresponding value in the “return_array." The second half of the equation speaks to the rules Excel should follow in its search: the “if_not_found” argument is the default value XLOOKUP returns if it can’t find the “lookup value” within the data, “match_mode” determines whether XLOOKUP searches for an exact match, approximate match, or a wildcard match, and “search_mode” determines if XLOOKUP searches for the data from top to bottom of the table, or vice versa.
Let’s use the gif above as an example. We have a table of sales data, and we need to return the Sales Representative that corresponds to Reference ID: 346533. To find this information using XLOOKUP:
- Enter the Function =XLOOKUP in the empty Sales Representative return cell
- Select the Reference ID cell as the lookup_value - that’s the value we’re searching for
- Select the full Reference Column as the lookup_array - that’s where we’ll look for the data
- Select the full Sales Representative column as the “return_array” - that’s the information we’re looking for! Which Sales Representative corresponds with the entered Reference ID?
- Close your parenthesis, hit enter, and you’re ready to easily see which Sales Representative corresponds to Reference ID: 346533 - and any other reference ID you’re looking for 😉
XLOOKUP is super easy to use, and it can do soooo many things that VLOOKUP can’t. We’re talking searching rows or columns, returning values *in any direction* from the Iookup_value, searching from the last value, returning a default value, returning multiple results, and more. She’ll make you forget all of your other search functions 😍
Want to know even more about how Team XLOOKUP ✖ dominates the Excel search game? Check out my VLOOKUP vs XLOOKUP YouTube series 🎥
