VLOOKUP Tutorial

This tutorial is written for someone who has never written a VLOOKUP formula before. Rather than jumping straight into syntax, it starts with what VLOOKUP is actually doing behind the scenes, then builds up to a complete worked formula using a small product table.

By the end, you'll be able to write your own VLOOKUP from scratch, recognize the most common errors before they happen, and know when it makes more sense to use Turbo Excel VLookup instead of a formula.

VLOOKUP stands for "vertical lookup." It scans down the first column of a range you choose, looks for a value you specify, and then, once it finds a matching row, returns a value from a column you choose within that same row.

The "V" matters: it only searches vertically down a single column, which is why the lookup column always has to sit at the left edge of your selected range.

lookup_value is what you're searching for. table_array is the full range containing your data, including the lookup column and every column you might want to return from. col_index_num is a plain count of columns starting at 1 for the first column in table_array. range_lookup is TRUE for an approximate match or FALSE for an exact match - almost always you want FALSE.

Here's a small product table to practice with. The goal is to look up the Price for SKU-2202:

Column A holds the SKU, and Price is the fourth column counting from A, which is why col_index_num is 4.

#N/A appears when there's no exact match - check for extra spaces, mismatched text/number formatting, or a simple typo in the lookup value.

#REF! appears when col_index_num points past the last column in table_array, usually after columns are deleted or the range is resized without adjusting the number.

Once you understand VLOOKUP, you'll notice how much of the process is repetitive setup: selecting ranges, counting columns, copying formulas down. Turbo Excel VLookup automates that setup - you choose the matching columns from dropdowns and tick which columns to return, and it applies the match across every row in one action.

A surprising number of VLOOKUP problems turn out to be a data type mismatch rather than a logic error - a value stored as text in one column and as a number in the other will never match, even if they look identical on screen.

Before assuming the formula itself is wrong, select the lookup value's cell and check the small green triangle or the number alignment; numbers align right by default, and text aligns left, which is a fast way to spot the mismatch.

In a shared workbook, a bare VLOOKUP formula gives the next person no indication of what it's supposed to be matching or why. Adding a cell comment, or a short label in an adjacent column, explaining the source and purpose of the lookup saves significant time when the workbook is revisited months later or handed to a colleague.

This becomes even more important once a workbook has several lookups feeding into each other, since untangling a chain of undocumented formulas is far harder than reading a documented one.