How VLOOKUP Searches a Table

VLOOKUP stands for Vertical Lookup. It searches down the first column of a table for a value you give it, then returns something from the same row in another column.

Think of a phone book: you find a name, then read across to get the number. VLOOKUP does exactly that for your spreadsheet.

The V reminds you it searches vertically (down a column). In the next scenes you will learn its four parts and use it on a real price table.

VLOOKUP takes four pieces of information, separated by commas:

• lookup_value - what you are searching for
• table_array - the range of cells that holds your data
• col_index_num - which column number to return from
• [range_lookup] - TRUE for approximate, FALSE for exact match

The square brackets mean the last argument is optional, but you should almost always set it. Here is the shape of the formula:

Imagine a small product table in cells A1:C4:

• Row 1 headers: Code, Name, Price
• Row 2: A100, Apple, 0.50
• Row 3: B200, Banana, 0.30
• Row 4: C300, Cherry, 1.20

The first column (Code) is where VLOOKUP will search. The other columns hold data you can return. We will look up a product by its code and pull back its price.

To find the price of code B200, we search column 1 for B200 and return column 3 (Price):

Reading it aloud: look up the value "B200" in the table A1:C4, and when found, return the value from the 3rd column, using an exact match (FALSE).

The result is 0.30. VLOOKUP found B200 in row 3, then read across to the third column.

The col_index_num is counted from the left edge of your table_array, not from column A of the sheet.

In our range A1:C4, the columns are numbered:

• Column 1 = Code (the search column)
• Column 2 = Name
• Column 3 = Price

So to return the Name, use index 2; for Price, use index 3. Index 1 simply returns the value you searched for.

Hard-coding "B200" is rare. Usually the value you want lives in another cell. Suppose someone types a code in E2. Point VLOOKUP at that cell instead of a fixed text.

Now whenever E2 changes, the result updates automatically. This is how lookups power invoices, dashboards, and search boxes.

VLOOKUP has one strict rule: it can only search the leftmost column of the table_array. It cannot search column 2 and look back to column 1.

That is why the column you want to search must be the first column of your range. If your codes are in column B, start your table_array at column B, like B1:D4.

This left-column limit is the most common cause of VLOOKUP frustration, and a later lesson covers how to work around it.

You can include or exclude the header row in your table_array. Both work:

• A1:C4 includes headers (Code, Name, Price)
• A2:C4 excludes headers

With an exact match (FALSE), headers do not cause wrong answers because they will not match a product code. Many people include them so the range is easy to read. Just remember the column index is still counted from the left of whatever range you chose.

Say you are building an invoice. The product code is in A10 and you want its name and price pulled from the table.

Name in B10:

Price in C10:

One table feeds many cells. Typing a code once fills in the rest, which is the everyday power of VLOOKUP.

Did you notice the $ signs in $A$1:$C$4? When you copy a VLOOKUP down a column, you want the lookup value to shift (A10, A11, A12...) but the table to stay fixed.

Absolute references with dollar signs lock the table in place. Without them, copying down would drag the table off your data and produce errors. Lock the table_array; leave the lookup_value relative.

Your data table often sits on a different tab. To reference a range on a sheet named Products, put the sheet name and an exclamation mark before the range.

If a sheet name contains spaces, wrap it in single quotes, like 'Price List'!A:C. The lookup works exactly the same, it just reads from another tab.

Test your understanding of how VLOOKUP searches.

You now know the core of VLOOKUP:

• It searches down the first column of your table_array
• It takes four arguments: lookup_value, table_array, col_index_num, and range_lookup
• col_index_num is counted from the left edge of the range
• Use FALSE for an exact match in most cases
• Lock the table with $ so it stays put when you copy down

Next you will dig into the difference between exact and approximate matching.