Hi! Welcome to The Data Are Alright, a blog about tips and tricks for changemakers. According to my stats, most of you found your way here through Google. I hope you enjoy the post below. If you’d like to stick around, here are a couple of great options to read. You can even SUBSCRIBE, which would absolutely make my day! (I post approx 3x / month)
- How social justice makes me a better database admin
- Why changemakers need good data
- 4 part series on Mail Merge
- 3 part series on digital self-care
- Clipboards and Megaphones: a new perspective on Dr. King’s legacy
The xlookup formula made quite a splash when it hit the scene in Aug 2019 (729k views on the Microsoft Blog, many viral posts on spreadsheet Twitter) but it wasn’t until last week that I got up close and personal with the new kid on the grid, thanks to my spreadsheet soulmate who gave me a generous demo during co-working time.
The ideas here are really the result of Emily’s brilliance/hard work/chutzpah/growth mindset. Since I felt so inspired, we agreed that I could write them up!
Things are v-looking up…
… and I’m not just saying that bc I got my second COVID vax dose today! VLOOKUPs are one of my favorite and most handy spreadsheet formulas, truly a game changer. I use them every day in my work as a database manager because I’m always taking data from different spreadsheets and splicing them together. However, they also come in handy when I moonlight as a volunteer organizer.
Here’s an example of a perfect scenario for using a VLOOKUP.
The Zoning Board is making a decision on whether or not they will allow a new pop-up dog park to stay operating in Zip Code 10001. We want to email all the doggies in that zip code with a pup-date about the proceedings. We’re hoping to send them a personalized (doggie-alized) email with their first name to attract their attention. When we look through our data, we find one list that has First Name and Email (but no zip code) and one list that has Email and Zip Code (but no name). What’s an organizer to do?
If your answer is “forget the first name!” or “retype the data,” I have good news for you 🙂
This is where the VLOOKUP comes in handy! The VLOOKUP formula is an organizer’s best friend (besides your dog, of course). All you need to do is tell the VLOOKUP which column has the matching data (in this case, email is the overlapping column) and it will scan the column vertically (get it? that’s the V in Vlookup) until it finds a match. Then it will move over based on your criteria, find the data, and bring it back. VLOOKUP, go fetch!
As you can see, I asked my VLOOKUP formula to find “scruffy@dog.com” (aka the email in cell B2) in the list of emails in Column E. Once it finds a match, I asked it to go over 2 columns to find the zip code, and bring it back. Good job, here’s a treat!
This is a simple example – everyone’s emails follow the same format, there are only 3 dogs, and the tables happen to be in the same order. But in real life, we know that things get very complicated quickly!
VLOOKUP rules
- You must have two tables that “share” a column of data. In this case, the column in common is email. Often, you want a “unique identifier” because if you use something as common as name, you might have the same name show up twice, but in different zip codes. VLOOKUP isn’t smart enough to deal with duplicates… it just takes the first one it finds. Sometimes I even make my own shared column by adding a “First + Last + Date of Birth” (should be unique enough for most cases!) if there isn’t an obvious one between two spreadsheets or systems.
- VLOOKUPS only search vertically. If you want to search horizontally (and there are some good reasons why you might want to do that, such as data that are organized according to months…), you gotta switch over to the HLOOKUP function.
- You must move from Left to Right when you are returning data. So, you always start with your shared column in the left of both tables, and then move your way right as you count the columns. Sometimes this means reorganizing your data to make it conducive to the VLOOKUP format.
- VLOOKUP does not like change… kind of like us, huh? If you change the order of your columns, add columns, or generally futz with your table, your VLOOKUP might break.
- VLOOKUP is case insensitive. So if capitalization should change the result in your shared column, you might need to find a different shared column!
- Ok, this isn’t a rule but it is a suggestion: When you are selecting your data in step two of your VLOOKUP formula, select the ENTIRE COLUMN (ie A:A) instead of specific cells (ie A2:B4). In the example above, I used specific cells because I wanted to make it super visually clear. But if I added another row to my data (more dogs = more fun) then the VLOOKUP would not capture them. Boo!
- Don’t be dissuaded by errors. VLOOKUPs can be finicky and when they don’t find results, they give you computer barf error codes like #N/A or #ERROR. These are easy to search for (or ask me). The most common error is selecting only one column rather than an entire table to look in OR that the value you are looking for (from Table 1) simply doesn’t exist in Table 2. There are workarounds for this… potential topic for a future blog post!
See XLOOKUP in action!
XLOOKUPs are an improvement on some of the VLOOKUP’s shortcomings. Let’s see it in action!
This might not seem like a big deal, or it might not even look different to our unpeeled eyes. And that’s ok! I’m going to point out the big ole thing that changed so that we can get excited about it together. The “Email” column (our Common Column!) is on the RIGHT SIDE OF THE SECOND TABLE!!!!
Plus, the XLOOKUP lets us include a response for if the formula does not find a corresponding response (in this case, “no zip”). In VLOOKUP land, you need to handle the “N/A” situation differently… by wrapping your VLOOKUP formula inside of an “IF ERROR” formula, like a hug from a kind of awkward, distant family member.
But wait, there’s more!
XLOOKUP can do all sorts of fancy things that VLOOKUP and it’s less-common sibling formula HLOOKUP (Horizontal) can’t do. Here’s some highlights. (Learn more at this very interesting blog post!)
- You don’t need to end the formula with “True” or “False” (I’ve literally always used “False” in my VLOOKUPS … tell me I’m wrong?) so it seems irrelevant
- You can go Vertical or Horizontal (but not both). Yes! The XLOOKUP can go in either direction!
- You can determine if you want to search Last To First instead of First To Last (in VLOOKUP, you would have had to re-sort your data)
- You can include multiple criteria!!!! (This totally blew my mind – scroll down in the blog post linked above if you want to be equally impressed)
- You can skip columns of data that you don’t need to return! (In VLOOKUP, you had to select every column between your Common Column and the farthest right-ward column that you needed to return. In XLOOKUP, you can select just the columns you care about).
Do you think the XLOOKUP is too good to be true? Rest assured, there are a few limitations:
- You and ALL of your spreadsheet collaborators must be on the latest version of Microsoft Excel to use it. If your collaborator doesn’t use the latest version, when they open your doc (attachment), it will show errors. Womp womp.
- If it is important to you to preserve the “integrity of your table” to be able to visually scan for connections or to use your lookup table for another purpose, or because you just like it that way, then XLOOKUP might not be for you!
- XLOOKUP is also not compatible with Google Sheets (last updated: 4/12/21)
XLOOKUP in Google Sheets
Are you a Google Sheets lover? Cool, me too. You’re in the right place! There’s no such thing as “XLOOKUP” specifically in Google Sheets (yet), but Emily found a workaround that is JUST as good. Emily… is BRILLZ yall! They call this the “Imaginary Table” trick.
What’s this? Yes, it’s like the ultimate mashup of XLOOKUP and VLOOKUP, this time in Google Sheets.
Let’s breakdown the parts of the formula:
- =VLOOKUP (ok, this is the basic formula call)
- B2 (this is the value we’re going to look for)
- {F:F, E:E} (this is our IMAGINATIONARY TABLE ARRAY!!!!! Where Google knows that “column 1” is F and “column 2” is E)
- don’t forget the curly brackets! {{{ }}}
- 2 (this is where we say which column the return data should come from
- My rule of thumb is that VLOOKUPs always end with “False.” Maybe one day I’ll write a blog post on True, but I would need to do some research because I’ve never used it!
Et tu?
How are you using VLOOKUPs, HLOOKUPs, or XLOOKUPs? Leave me a comment on this post! I would loooooove to do a follow up post with some real, juicy social justice examples. Don’t be shy!