VLOOKUP Column Index Number Explained

The VLOOKUP column index number is the third argument in the formula, and it tells Excel which column to bring back once it finds your value. You count it from the left edge of the table you selected, starting at 1, not from column A of the sheet. This guide shows how to count it, what the #REF! and #VALUE! errors mean, and how to make the number update by itself.

What is the VLOOKUP column index number?#

The column index number (Microsoft calls it col_index_num) is a whole number that picks a column inside your table. VLOOKUP has four arguments:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

VLOOKUP always searches the first column of the table and then moves right by the number of columns you give. If you are new to the function, start with the step-by-step VLOOKUP guide for beginners.

All the examples below use this product table in A1:D6:

How to count the column index number#

Put your finger on the first column of the table and call it 1, then count right until you reach the column you want. With the table A2:D6, ID is 1, Product is 2, Category is 3 and Price is 4.

A column index number of 1 just returns the value you looked up. That is only useful as a quick check that the value exists.

The FALSE at the end matters as much as the number. Without it, VLOOKUP uses an approximate match and can return the wrong row. The guide to VLOOKUP exact match and why FALSE matters explains this.

The table doesn't have to start in column A#

The count always starts at the first column of table_array, wherever that is. This is the most common source of wrong answers. Say you look up a product name instead of an ID, so the table starts in column B:

=VLOOKUP("Monitor Stand",B2:D6,3,FALSE)

Here column B is 1, C is 2 and D is 3, so the result is 39, the price. If you counted from column A out of habit and typed 4, you would get #REF!, because B2:D6 is only three columns wide.

A quick way to check: select the table range in the formula bar and look at the tooltip, or count the letters. B to D is three letters, so the largest valid number is 3.

Why does VLOOKUP return #REF! or #VALUE!?#

The column index number must be at least 1 and no bigger than the number of columns in the table. Outside that range, VLOOKUP returns an error:

The negative number case is worth remembering. VLOOKUP can only return columns to the right of the lookup column. To return a column on the left, use XLOOKUP or INDEX MATCH. The comparison of VLOOKUP vs INDEX MATCH shows both on the same data. For other errors, see why VLOOKUP is not working and how to fix it.

Why a typed column number breaks when you insert a column#

A typed number such as 4 never changes, even when the table does. Suppose you insert a new column, Supplier, between Category and Price. Excel stretches the table reference from A2:D6 to A2:E6, but the 4 stays a 4. Column 4 is now Supplier, so your price formula quietly returns supplier names.

There is no error message, which makes this mistake easy to miss. Two fixes keep the number correct:

1. Look up the header with MATCH, so the number follows the column.
2. Use XLOOKUP or INDEX MATCH, which point at the return column directly instead of counting.

How to make the column index number dynamic with MATCH#

MATCH finds the position of a header in the header row, and that position is exactly the column index number you need. In the product table:

=VLOOKUP("P-103",A1:D6,MATCH("Price",A1:D1,0),FALSE)

MATCH("Price",A1:D1,0) returns 4, because Price is the fourth header. VLOOKUP then returns 39. If someone inserts a column before Price, MATCH returns 5 and the formula still finds the price.

Make sure the header row in MATCH starts in the same column as the table. If the table starts in column A, the header range must start in column A too, or the count will be off by one.

You can also put the header name in a cell, such as H1, and write MATCH(H1,A1:D1,0). Now you can change H1 from Price to Category and the same formula returns a different column.

How to use COLUMN when you copy a VLOOKUP to the right#

When you need several columns for the same ID, COLUMN can supply the number for you. COLUMN(B1) returns 2, because B is the second column of the sheet. Copy it one cell to the right and it becomes COLUMN(C1), which returns 3.

=VLOOKUP($F2,$A$2:$D$6,COLUMN(B1),FALSE)

Type this in one cell, select it with the two cells to its right, and fill right (Windows: Ctrl+R; Mac: ⌘R). The first cell returns the product name, the next the category and the third the price. The $ signs keep the lookup cell and the table fixed while the column number grows. This trick only works when the table starts in column A, because COLUMN counts from column A of the sheet. The guide to returning several columns with one VLOOKUP covers this and other methods. For a quick syntax reference, see the VLOOKUP function guide.

Practise it#

Counting columns is easy to read about and easy to get wrong under time pressure in a job test. Practise VLOOKUP in a live spreadsheet: you type the formula, and it checks the column index number and the result instantly. Then work through more VLOOKUP practice tasks with tables that don't start in column A.