Dear Analyst #34: Trick for finding column index for VLOOKUPs using pride events data

This is one of my favorite VLOOKUP tips. Given that it’s pride month, we’ll be applying this tip to a list of all pride events in the United States. Here is the Google Sheet if you want to follow along with this example. Here’s the scenario: you have a super large table in Excel or Google Sheets (by large I mean there are many columns) and you need to do a VLOOKUP on the 25th column. Instead of counting 25 columns from the left of your lookup column, you can use this column index trick to quickly get the column you’re after.

Creating column indexes above your lookup table

In the screenshot above, you’ll notice that each column has the column index above it. This is a simple formula of the previous column index added to 1:

This might feel a little strange because we’re used to heaving the column headers in the first row of our table. By having this column index in above the column header, however, it’ll make it easier to provide the col_index parameter you need to provide to your VLOOKUP formula. In this list of pride events, if I want to get the Start column pulled into my VLOOKUP formula, I simply reference the column index above the column header instead of writing out the number “5” (note that PrideEvents is a named range representing A2:E270 in my list of pride events):

Putting the column index above your new column headers

In this second example, I put the column index above the new table where I want to pull in data from my list of pride events. Notice that the order of columns I want to pull does not match the column order from my lookup table. The simple trick here is that I do a simple cell reference to the column index above the main table so that I know that the order of the columns I want to pull back in this case is 3, 5, 2:

One of the benefits of this trick is that you can move columns around in your lookup table and this VLOOKUP formula will still work only if you “reset” the column indexes above your lookup table column headers to be sequential (1, 2, 3, etc). This is kind of annoying because any time I switch columns around, I have to re-drag the formula of the previous cell plus 1 in row 1 where my column indexes are. Hopefully your columns aren’t moving around too much and this solution works for you.

Using the MATCH() function to find the column index

This is a little more advanced, but another solution is to use the MATCH function to match the column name in your new table with the column names in your lookup table:

Instead of doing a simple reference to the column index in that first row in my new table, I have this MATCH function which tries to match Location, in this case, with the column headers in the lookup table ($A$2:$E$2 represents the column headers from my list of pride events). If it finds a “match,” then the MATCH function returns back the column index. You could actually do this solution without having that column index above your new table columns by putting the MATCH function directly in your VLOOKUP formula, but it might make the formula more difficult to debug in the future.

Pride Easter egg in Google Sheets

To celebrate pride month, here’s a fun Easter egg you’ll find in Google Sheets if you type out “PRIDE” in separate columns (you’ll also see this in the Google Sheets example for this blog post):