VLOOKUP like the Pros

Make your data sing in Sheets

But first, a video intro…

VLOOKUP is the gateway drug of spreadsheet formulas.

It opened my eyes to the power of a simple spreadsheet.  For very little work up-front - writing one little formula - you can save hours in data entry.

It was *magic*, and led me on the journey of learning Google Sheets, SQL, Blockspring and Supermetrics.

But without that first taste of it, I wouldn’t have been hooked.

If you’re already up to speed with VLOOKUP, you may be ready for the big kahuna of Sheets functions - the QUERY.

An ode to VLOOKUP

If you’ve only written vanilla VLOOKUPs before, you may not know that you can get *really* creative with this humble formula.

The VLOOKUP cheat sheet (grab your copy on Trello here) walks through 6 ways that pro spreadsheet users take advantage of VLOOKUP.

1. The simple formula

=VLOOKUP(B10, ‘Sample Tweets’!C:E, 3, 0)

Don’t sleep on this one - a well-written lookup formula is the foundation of any more creative usage.  If your base formula is off, you’ll be crying when you go to layer on other formulas.

2. Copy-pasting to an entire column using ARRAYFORMULA

=ARRAYFORMULA( VLOOKUP( B11:B15, ‘Sample Tweets’!C:E, 3, 0))

Write once, run everywhere.  ARRAYFORMULA allows you to set a lookup across an entire column, without copying and pasting the formula into each cell - keeping your Sheets nice and clean.

3. VLOOKUP from another Sheet

=VLOOKUP(B11, IMPORTRANGE( ‘1sRs_V09LAODy0Nod3xKXN98bX9s7guhReQ6wBa8Kwcw’, ‘ ‘Sample Tweets’!C:E ‘ ), 3, 0)

This is where Google Sheets separates itself from Excel.

Since any Google Sheet can import data (using IMPORTRANGE) from any other Sheet, you can run it on data from outside your current Sheet.

4. VLOOKUP on multiple criteria with ARRAYFORMULA

=VLOOKUP( B11 & C11, { ARRAYFORMULA (‘Sample Tweets’!C:C & ‘Sample Tweets’!I:I ), ‘Sample Tweets’!B:B }, 2, 0)

Because VLOOKUP is so simple, it’s very easy to trick into doing thing it’s not specifically built for.

You can combine multiple columns (concatenate them essentially) before running the lookup, which will trick the formula to look for matches on both criteria.

5. Reverse VLOOKUP

=VLOOKUP ( B11, {‘Sample Tweets’!C:C, ‘Sample Tweets’!A:A }, 2, 0)

One of the pains with VLOOKUP is that it can only look up values left-to-right - the value you’re looking up against has to be in the leftmost column of your range.

But - what if you rearrange your, er, range?  Using the handy curly braces { } in Google Sheets, you can recombine columns in an order that works with VLOOKUP.


=HLOOKUP( ‘handle’, B11:G13, 3, 0)

If your data is oriented the wrong way for VLOOKUP, you can instead use it’s close cousin, HLOOKUP.

For example, if you have dates in the header row of your Sheet, and you’re looking to pull the value in a specific row, for a specific date, you’d HLOOKUP for that date, and then pull the Nth row.

Your turn!

Dive into the VLOOKUP cheat sheet for yourself, and while you’re at it you can browse the rest of the CIFL Sheets templates + tutorials on Trello.