VLOOKUP col_index_num: How to Choose the Right Number

Formulas & functions · Updated November 18, 2026

In Excel’s VLOOKUP function, col_index_num is the number of the column to return from the selected table range, counting from that range’s left edge. Use 1 for its first column, 2 for its second, and so on; for example, if the lookup range is A2:C5, a value of 3 returns a result from column C.

What col_index_num means

VLOOKUP searches down the first column of a range for a lookup value, then returns a value from the same row in another column. The col_index_num argument tells Excel which column in that range contains the result. It is a position within the range, not the worksheet’s column number or letter.

This distinction is the source of many VLOOKUP mistakes. If your table range starts in column B, then B is position 1, C is position 2, and D is position 3. The worksheet column letters do not change the count. Likewise, the index is not the row number of the result: VLOOKUP finds the row, while col_index_num identifies the return column.

The function’s argument order is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The first argument is what to find, the second is the full range to search and return from, the third is the return-column position, and the optional fourth argument selects exact or approximate matching.

How to count the return column

Start counting at the leftmost column included in table_array, even if that column is not A and even if it contains the lookup key rather than the information you ultimately need. Count each column across until you reach the field you want returned.

  1. Identify the full table range that VLOOKUP will use.
  2. Confirm which column in that range contains the lookup values. It must be the range’s first column.
  3. Count from that first column to the column containing the desired result, including both ends.
  4. Use that count as col_index_num.

For instance, in D2:G100, D is 1, E is 2, F is 3, and G is 4. If the lookup key is in D and the return value is in F, the correct index is 3. If you instead set the table range to E2:G100, then E becomes 1, F becomes 2, and G becomes 3. Changing the left edge changes the count.

A worked Excel example

Suppose a small table has product IDs in A2:A5, names in B2:B5, and stock status in C2:C5. The rows contain 104 / Mira / Active, 108 / Omar / Review, 112 / Lena / Active, and 119 / Noel / Closed. Cell E2 contains the product ID 112. With that layout, the table range is A2:C5: IDs are in position 1, names in position 2, and stock status in position 3.

With IDs in A2:A5, names in B2:B5, stock status in C2:C5, and the search ID in E2, use:

=VLOOKUP(E2,$A$2:$C$5,3,FALSE)

The formula returns Active, the value in column C on the row where Excel finds 112. The dollar signs keep the table range fixed if you copy the formula to another cell; they do not change how the column index is counted. If you wanted the product name instead, the return column is position 2.

With the same table and search ID in E2, use this formula to return the name from column B:

=VLOOKUP(E2,$A$2:$C$5,2,FALSE)

It returns Lena. The lookup value remains in E2, but the index changes because the requested return field is now the second column of A2:C5.

Exact match and approximate match

The fourth VLOOKUP argument does not set the column index; it controls how Excel matches the lookup value. For most lookups involving IDs, names, account numbers, or other distinct keys, specify FALSE for an exact match. You can use 0 as an equivalent value. If you omit the fourth argument, Excel uses approximate matching, which is a frequent source of unexpected results.

Approximate matching with TRUE (or 1) is intended for ordered ranges, such as threshold tables. Excel looks for an exact value or, when there is no exact match, the largest first-column value that is less than the lookup value. The first column must be sorted in ascending order for dependable approximate results. If the lookup value is below the smallest entry, VLOOKUP returns #N/A.

For example, with IDs 104, 108, 112, and 119 in ascending order in A2:A5, names in B2:B5, and a search value of 110 in E3, approximate matching returns the row for 108. With that layout and the search value in E3, this formula returns Omar:

=VLOOKUP(E3,$A$2:$C$5,2,TRUE)

This is appropriate only if the approximate, lower-value match is what the task calls for. It is not a safe substitute for exact matching when searching for a particular product ID: a mistyped or missing ID could otherwise produce a plausible value from a different row.

Errors and wrong results to check

A wrong col_index_num can return a valid-looking value from the wrong field, so not every mistake produces an error message. Check the table boundaries and the count whenever a result looks suspicious. These are common issues:

  • #REF!: The index is greater than the number of columns in table_array. For a range with three columns, an index of 4 has no corresponding return column.
  • #VALUE!: The index is less than 1, or an argument has an unsuitable value. An index of 0 cannot request the lookup column.
  • #N/A: An exact match was not found in the first column of the selected range. The value may be absent, formatted differently, or outside the range.
  • A value from the wrong field: The index was counted from the worksheet’s A column instead of from the start of the selected range, or the count itself is off by one.
  • A surprising approximate result: The fourth argument was omitted or set to TRUE, or the lookup column is not sorted as required.

Also confirm that the lookup column is actually the first column in the selected range. VLOOKUP cannot search a column in the middle of the range and return a value to its left. If your key is in C and the desired result is in A, changing the index will not make that lookup work; you need to rearrange the lookup range or choose another function.

What happens when the table changes?

The index is a number, so it is worth revisiting formulas when columns are inserted, removed, or the range is edited. If you originally count the third column as the return field and later include an additional column between the lookup field and that field, the third position may now refer to different information. The formula can still calculate without an error while returning the wrong field.

Using a clearly bounded range and readable headers helps make the count easier to review. You can also check the formula in the formula bar and select its table_array range to see which columns it includes. For repeated formulas, lock the range with dollar signs, or consider an Excel Table so the data range can expand as rows are added. A table does not remove the need to count the VLOOKUP index, however.

When the source table is rearranged frequently, a hard-coded number is less self-explanatory than a lookup method that identifies the return range directly. In any case, test a formula against a row whose correct answer you know, especially after changing the source columns.

VLOOKUP compared with newer lookup options

VLOOKUP remains useful when the lookup key is in the leftmost column and the result is to its right. Its numbered-column design is familiar and works in many Excel versions. Its limitation is that the return field is identified by position, so changing the range or its columns can make formulas harder to maintain.

In Microsoft 365, XLOOKUP lets you specify a lookup range and a return range separately, can return values to the left, and does not require a col_index_num. It is also available in Excel 2021, but not in older perpetual versions such as Excel 2019. With IDs in A2:A5, stock status in C2:C5, and the search ID in E2, this formula returns the matching status or a message if no match exists:

=XLOOKUP(E2,A2:A5,C2:C5,"Not found")

Another option is INDEX with MATCH, which can also separate the return range from the lookup range and work in older Excel releases. Which approach is best depends on workbook compatibility and how the data is arranged. If a workbook must be opened in older Excel versions, VLOOKUP may be the more compatible choice; if it is maintained in Microsoft 365, XLOOKUP can make the requested columns more explicit.

Frequently Asked Questions

Does col_index_num start at 0 or 1?
It starts at 1. The leftmost column in table_array is 1, and 0 is not a valid return-column index.

Is col_index_num the same as a worksheet column number?
No. It is the position inside the selected table range. In a range beginning in column D, column D has index 1, regardless of its worksheet position.

What col_index_num should I use for column C?
It depends on where the table range starts. If the range begins in A, C is position 3; if it begins in B, C is position 2. Count from the range’s left edge.

Why does VLOOKUP return the wrong column?
The index may be off by one, the range may start in a different column than expected, or the source table may have changed. Count the columns within the exact table_array used by the formula and verify the result field.

Can VLOOKUP use a negative col_index_num to look left?
No. The index must be at least 1, and VLOOKUP only returns from a column to the right of, or including, its first lookup column. Use XLOOKUP or another lookup method when the result is to the left.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.