Leveling up your VLOOKUPs

“They do VLOOKUPs all day long. That’s all they do on Excel.”

I was helping a friend learn how to use Excel functions, one of which was the VLOOKUP. I was trying to get a sense of how her boss and colleagues approached using Excel so I could train her in the way they did things. It sounded like they were doing things in a very manual way.

Each day they would download the data and then do all sorts of VLOOKUPs. Her boss said, “I do VLOOKUPs all day long.” From her telling, it sounded like multiple times of day for different types of downloads.

And each day they would start over again with the VLOOKUPs.

In addition to that, the downloaded data had so many columns, like past column Z, that they were doing a lot of counting. If you know how VLOOKUP is structured, you know you would need the column number of the range of the desired response for VLOOKUPS (HLOOKUPs would require row number).

Can you say Groundhog Day?

In this post, I will address the counting because I have done this a lot of times myself: counting a lot of columns to get the number for the required information to supply to VLOOKUP.

3 Options I Have Thought Of

There may be many ways of handling VLOOKUPs but right off hand, I have 3 in mind. But before I begin, let’s set up an example file so you can follow along. This file is a snapshot of an election file pulled from MIT.

Here’s the citation from the site: MIT Election Data and Science Lab, 2018, “County Presidential Election Returns 2000-2016”, https://doi.org/10.7910/DVN/VOQCHQ, Harvard Dataverse, V6, UNF:6:ZZe1xuZ5H2l4NUiSRcRf8Q== [fileUNF]

We will build up a VLOOKUP formula to find the median income for Blount County, Alabama. The search key will be the “fips” because there is a unique fips for each county-state combo. The fips for Blount County Alabama is 1009, under column C. The median income is found in column AF.

The normal VLOOKUP formula would be written as:

=VLOOKUP(AP5, C2:AM3115, 30, FALSE)

AP5 would be where I would enter the fips of the desired county-state that I’m searching. The range C2:AM3115 contains a table of all of the different types of data that we could query. I started out with column C because the first column of the range must be the item we are asking questions about, in this case the county-state or fips. Column C is numbered as the first column. The number 30 represents column AF. If you start with 1 at column C, the beginning of C to AM range, the 30th column of the C to AM range is AF. And then finally, false is an exact match.

As you can see, in order to get the column number, I would have to count each column to determine what to put in the VLOOKUP formula. Multiply that out to multiple times a day, every day, and then we are talking a lot of counting.

Option 1

This is a really easy option and can be done quickly.

Option 2

This is a nice easy way of setting up the VLOOKUP because the advantage of this set up is if you ever insert or delete a column in the table range, you don’t have to remember to go back into the VLOOKUP formula and change the number 30 to a new column number. The formula COLUMN automatically adjusts when you add or delete a column.

Option 3

I’m not going to explain much on the parts of the formula because there are plenty of sites that can do a much better job of explaining. My only intent with this post is to offer another alternative to counting for VLOOKUP. This relatively new function is a very nice function. If you subscribe to Office 365, you should be using this function instead of VLOOKUP.

Closing

I actually wrote this in a PDF so I will add it here. In the PDF, I was trying to show the concepts with as little words as possible but it’s a work in progress.

But the key point is you don’t have to count your columns, especially if you have a lot of columns. There are easier ways to get at it.