Since TikTok is in the news right now about who is going to buy them, I thought using some fake-ish TikTok acquisition data would be relevant for this episode. A classic Excel/Google Sheets challenge: how to do a VLOOKUP to the "left" e.g. your lookup column is not the first column in your lookup table. There are all sorts of strategies to overcome this issue with how your data is structured. Notably, the INDEX/MATCH strategy is the most commonly-cited strategy when good 'ol VLOOKUP is not at your disposal. In this episode I walk through a strategy that allows you to use VLOOKUP: array formulas. Skip to strategy #3 below if you want to see the answer. Associated Google Sheet for this episode if you want to follow along.

Was trying to find some gif associated with "looking up"

See the video below if you want to jump straight to the tutorial:

https://www.youtube.com/watch?v=6JluR45VJl4

Why the VLOOKUP won't work

If you are new to why VLOOKUP won't work in this scenario (see Google Sheet), take a look at the data data structure below:

We have ID in column A and we want to find Company Name and Market Cap in columns C and D, respectively, for these IDs. The ID in column A is the unique identifier for the row, and we need to do a lookup to Company ID in column I.

While you can eyeball the result for the first row ("Triller" is the company for ID 3), we want to find a scalable solution using formulas.

As you start writing the VLOOKUP formula in column C, you'll start to notice the problem: the Company ID column is not the first column in your table to lookup the ID value in column A:

Here are a few strategies for solving this problem (#3 is probably the one you haven't seen before).

Strategy #1: Move the lookup column to the first column position

This is not the most ideal solution, but you could just simply cut and paste the Company ID column and move it to the left-most "first" column of your lookup table. In Excel you would have to do a cut and paste, but in Google Sheets you can just drag and drop the column into the proper position:

Now the VLOOKUP for Company Name will work correctly since Company ID is the first column in your lookup table:

I don't like this strategy because it involves some manual cutting and pasting of columns. If your lookup table isn't static (e.g. might be sales data that gets added daily), then you might be ruining the "structure" of your data on subsequent updates. Let's see what else we can do.

Strategy #2: Make copies of the columns to the right of the lookup column

Also not an ideal solution, but it works in one-off cases where your data is static and you don't care about showing your back-end work to a colleague. It looks like data is duplicated, but you're basically referencing existing columns in your table so that those columns appear to the "right" of your lookup column:

Now you can do a VLOOKUP for columns I to K to get the Company Name and Market Cap values to show up in columns C and D: