- Got Sheet
- Posts
- Your Last VLOOKUP Tutorial
Sponsored by
In today’s email:
Master VLOOKUP
Master HLOOKUP
Master XLOOKUP (and get rid of the others)
SPONSOR
Got Sheet is powered by beehiiv, my go-to newsletter operating system for online businesses. Try for free here.
MAIN ARTICLE
VIDEO WALKTHROUGH
How to use VLOOKUP
Stop using VLOOKUP. Just use XLOOKUP. See below.
If you want to see VLOOKUP in action, watch my walkthrough video, but seriously, XLOOKUP does it better.
How to use HLOOKUP
Stop using HLOOKUP. Just use XLOOKUP. See below. If you want to see HLOOKUP in action, watch my walkthrough video, but seriously, XLOOKUP does it better.
How to use XLOOKUP
This is the way. XLOOKUP can do everything that VLOOKUP and HLOOKUP can do…and then some.
It’s better and more intuitive. It is easier to learn because it makes more sense out of the gate.
Trust me, I’ve been in your shoes. I’ve re-learned VLOOKUP a dozen times too. XLOOKUP is your new best friend.
We use lookup functions to look through one set of data, find a value, and return a corresponding value from another column or row of data.
The three required parts of XLOOKUP are the lookup_value, the lookup_array and the return_array.
Here’s the super simple, super small data set we’re using in our example:
Person | Music |
|---|---|
Adam | Shred |
Danny | Hit |
Justin | Thump |
Maynard | Vox |
We simply look for a person in the first column and return it’s matching value from the second column.
If we search for Danny, a lookup function will return Hit.
Lookup Value
The lookup_value is often a cell where we can type in a search term or select one from a dropdown list. This is the person we are searching for in our example.
Lookup Array
The lookup_array is the column (or row) that we want the function to search for our lookup_value. It’s column A in our example - all the people in the band.
Return Array
The return_array is the column (or row) that we are ultimately searching for information about. This is what will be returned after the function finds our lookup_value. This is the column B in our example - the Music.
Importantly, the lookup_array and the return_array have to be the same size.
This is where I’ve made the most mistakes when using lookup functions.
If we select four rows for our lookup_array, we’ve got to select four rows for our return_array.
Otherwise, they’re mismatched, and it will throw an error at you.
If Not Found
The first optional parameter is if_not_found. It let’s us write a custom value that will appear if the search term isn’t found.
If we search for Eamonn, then we can have this return something like “not in the band”.
If you leave this blank, it will simply return #N/A.
Match Mode
The second optional parameter is match_mode. The most infuriating thing about VLOOKUP was that it didn’t default to an exact match even though that’s almost always what we want to search for.
XLOOKUP defaults to an exact match. Rejoice!
If you need to change this for some reason, though, you can. And we have some pretty fancy options:
0 - Exact match. If none found, return #N/A. This is the default.
-1 - Exact match. If none found, return the next smaller item.
1 - Exact match. If none found, return the next larger item.
2 - A wildcard match where *, ?, and ~ have special meaning.
Search Mode
The final optional parameter is search_mode. This lets us search first to last, last to first, ascending or descending. First to last is the default.
XLOOKUP Walkthrough Video
Bookmark this walkthrough video. It’ll give you a reference to come back to until you’re confidently adding XLOOKUPs for all your colleagues.
NEXT STEPS
Whenever you’re ready, here’s how I can help:
Consulting (limited availability)
Grab a slot on my calendar for help with a spreadsheet OR newsletter project.Business tech stack (FREE)
My recommendations for software and tools I use to create content and run my online business.Sponsor Got Sheet
Got Sheet teaches business operators, teachers and entrepreneurs how to get good at spreadsheets. Interested in sponsoring an issue or a series of Got Sheet newsletters? Reach out to get more information.Personal budget tool
As a Got Sheet subscriber, I want you to have a 75% discount on the personal budget I built 10+ years ago and am still using to this day.If you aren’t subscribed yet, come on over to my YouTube channel where I make all my spreadsheet, coding and productivity tutorials
Reply