XLOOKUP is my Best Friend

For many years I stayed in the shallow end of spreadsheets. Things like Pivot Tables & VLOOKUP() scared me.

Like most things, it was simply because I hadn’t used them…because I hadn’t come across a reason for them yet.

Once I did, I found the beauty and satisfaction that so many a spreadsheet wizard before me had pontificated about.

XLOOKUP() is the latest iteration of VLOOKUP() and it improves upon it in almost every way.

I made a video about lookup functions in general if you want to go more in depth after reading this article…

What it Does…

The gist is this: you need to look up a value in a table. Here’s a bare bones example:

You’ve got a list of names with addresses, birthdays, etc. XLOOKUP allows you to look up the birthday or address (or any other piece of information) from a given name.

Here’s what it looks like in a spreadsheet.

XLOOKUP() needs a few arguments to work. At a minimum, it needs a search_key, a lookup_range, and a result_range.

The search_key is what you’re searching for. In the example above, it’s whatever is in C2 where I made a dropdown list of names.

The lookup_range, A3:A6, is where you’re going to look for the search_key. In this case it’ll be the names in column A.

The result_range, B3:B6, is where you’re going to return the result from. So when the function finds David in A3, it’ll return the corresponding value (his address) in B3.

This works vertically as well as horizontally.

If you have lookup values running across a row, as long as your lookup_range and result_range are the same size, you can do the same lookup like this:

Optional Values

There are optional arguments too. If no value is found, you can enter a custom message like I’ve done above: "not found".

You can also choose to get an exact match (this is the default), or for a match this is either exact, higher, or lower than the search_key. This is part of why I like XLOOKUP() so much more than VLOOKUP(). It defaults to an exact match where VLOOKUP() does not.

The last argument is the search_mode. This lets you search from start to end (default) or end to start. Again, the default start to end makes the most sense most of the time.

I hope this is helpful for you!

Who do you know that would like this newsletter? Forward them a copy! 🙏

______
👋 My name is Eamonn, and I love Google Sheets & Workspace. If you'd like more tips like this, subscribe to me on YouTube: https://www.youtube.com/@eamonncottrell?sub_confirmation=1

And join the conversation on LinkedIn:
https://www.linkedin.com/in/eamonncottrell/

Please ask questions in the comments, or send me a message; I love making new spreadsheet connections!
_______