Excel VLOOKUP Column Index Number Explained

In Excel VLOOKUP, the column index number tells Excel which column to return a value from, counting from the left edge of the table range you specify. The first column in that range is 1, the second is 2, and so on; the number is not the worksheet’s column letter or position.

What the column index number means

The column index number is the third argument in VLOOKUP. It is also called col_index_num in Excel’s function description. VLOOKUP first searches for a value in the leftmost column of its table range, then uses the index number to choose a result from the matching row.

For example, if the table range is A2:D5, its columns are counted like this: A is 1, B is 2, C is 3, and D is 4. If the range instead begins at column C, then C is 1, D is 2, E is 3, and F is 4. Excel counts within the selected range, not from worksheet column A.

This is the key point behind many VLOOKUP mistakes: the index is relative to table_array, not an absolute column number. A return value in worksheet column D needs an index of 4 if the range begins in A, but an index of 2 if the range begins in C.

How VLOOKUP uses the index

VLOOKUP follows a two-part process. It searches down the first column of the table range for the lookup value, then moves across the matching row by the number of columns indicated by the index. The lookup column must therefore be the leftmost column in the chosen range, and the index must refer to a column inside that range.

Suppose a worksheet has employee IDs in A, names in B, departments in C, and salaries in D. With IDs in A2:A5, names in B2:B5, departments in C2:C5, salaries in D2:D5, and the employee ID to find in E2, this formula returns the salary from the fourth column of A2:D5:

With employee ID 104 in E2 and the matching row containing salary 72000 in D5, the result is 72000.

=VLOOKUP(E2,A2:D5,4,FALSE)

The final argument, FALSE, asks for an exact match. The index number and the match setting do different jobs: 4 selects the salary column, while FALSE determines how Excel should match the employee ID.

How to choose the right number

To choose an index, identify the first column of the lookup range, then count across to the column containing the answer. Include every column in between, even if you do not need its contents. If your range is B2:F100 and the answer is in column E, E is the fourth column within that range: B=1, C=2, D=3, E=4.

A reliable way to work it out is:

Check the exact cells used for table_array in the formula.
Mark its leftmost column as 1.
Count right, one column at a time, to the return column.
Confirm that the index does not exceed the number of columns in the range.

For example, a four-column range permits index numbers 1 through 4. Index 1 returns the value from the lookup column itself; it does not mean “the next column.” If you need a result in a column that is not included in the range, expand the range or choose a different lookup method.

Exact match and approximate match

For IDs, names, product codes, and most ordinary lookups, use an exact match by setting VLOOKUP’s fourth argument to FALSE or 0. This prevents Excel from treating a nearby value as a match. In Microsoft 365 for Windows, the dialog may show the argument as range_lookup; FALSE means exact matching.

If the fourth argument is omitted or set to TRUE, VLOOKUP uses approximate matching. In that mode, the first column of the lookup range must be sorted in ascending order for predictable results. Excel returns an exact match when it finds one; otherwise, it can return the row for the largest first-column value that is less than the lookup value. That behavior can be useful for ordered bands such as tax brackets or discount thresholds, but can produce a misleading answer if the data is unsorted or the user expects an exact match.

Do not confuse the match setting with the index. Changing FALSE to TRUE does not change which result column is selected. It changes how Excel finds the row; the column index still selects the result within that row.

What happens when the table changes

A typed index such as 4 is easy to understand, but it can become wrong if someone inserts or rearranges columns in the source data. The range may expand automatically in some circumstances, yet the number you typed does not necessarily adapt to a changed meaning of the columns. It is worth checking both the range and index after editing a lookup table.

When a return column is identified by a header, MATCH can calculate its position within the header row. For example, with IDs in A2:A5, names in B2:B5, departments in C2:C5, salaries in D2:D5, headers in A1:D1, the lookup ID in G2, and the desired column heading Salary in H1, this formula obtains the index from the header and returns the matching salary:

For ID 104 in G2 and Salary in H1, MATCH finds Salary in the fourth position of A1:D1, so the result is 72000.

=VLOOKUP(G2,A2:D5,MATCH(H1,A1:D1,0),FALSE)

The zero in MATCH requests an exact header match. Keeping the header range aligned with the data range is important: A1:D1 has four positions, corresponding to the four columns in A2:D5. If you add or remove a source column, update both ranges so the returned index still points to the intended field.

Common errors and how to fix them

When a VLOOKUP result is unexpected, inspect the lookup range and the index before assuming the source value is missing. These common errors point to different problems:

#REF! often means the index is larger than the number of columns in the table range, or a referenced column was removed. Count the range columns again and make sure the desired return column is included.
#N/A with an exact match means Excel did not find the lookup value in the first column. Check for extra spaces, different data types, spelling differences, and whether the lookup column is actually the range’s leftmost column.
#VALUE! can appear when the index is less than 1. A column index must be a positive integer; use 1 for the first column of the range.
A plausible but wrong result can mean the index points to the wrong field or the formula is using approximate matching. Verify the column count and use FALSE for an exact lookup when appropriate.

Also watch for numbers stored as text. An ID that looks like 104 may be a numeric value in one place and text in another, and an exact lookup may not treat those entries as equivalent. Formatting cells to look alike does not always change the underlying value. Compare the source and lookup value types, and clean or convert the data when needed. If IDs can contain leading zeroes, treat them consistently as text so that a code such as 00104 is not accidentally reduced to 104.

VLOOKUP limits and alternatives

VLOOKUP can return values only from columns to the right of its lookup column, because it searches the leftmost column of the range and then returns from a column to its right or from that same column. A negative index is not a way to look left. If the return field is to the left, rearrange the data if practical or use another function.

XLOOKUP, available in Microsoft 365 and Excel 2021 or later, can search one range and return a value from a separate range, including a column to the left. It does not use a column index number, so inserting a column between lookup and return ranges is less likely to make the return position confusing. INDEX with MATCH is another established option for flexible lookups. Choose based on the Excel versions used by everyone who needs to open the workbook; newer functions may not work in older releases.

VLOOKUP remains useful when the lookup key is at the left of a compact table and the return column is stable. Its index is explicit and easy to audit once the range is understood. For workbooks shared across older Excel versions, it can also be a practical compatibility choice.

Practical checks before relying on a result

A lookup result is only as reliable as its source data and range. Before using the output in a report or calculation, check that each lookup key identifies the intended record and that the return field is the one you meant to select. If the first column contains duplicate keys, VLOOKUP returns the first matching row it encounters, not every match. Sort order does not change that first-match behavior for an exact lookup.

It is also useful to make the lookup range absolute when copying a formula down. In Excel, dollar signs lock a reference so that it does not shift as the formula is filled. For instance, select the range in the formula and press F4 on Windows to cycle through reference styles. On Mac, the equivalent shortcut depends on the keyboard and Excel settings; you can type the dollar signs directly. This adjustment affects which cells the formula refers to, not the meaning of the column index.

For a workbook that will be maintained by other people, make the index easy to verify. Keep clear headers, avoid unrelated columns inside the lookup range, and consider documenting which field the number represents. A formula returning the right value today can silently point to a different field after a structural edit, so a quick review after table changes is worthwhile.

Frequently Asked Questions

Is the VLOOKUP column index number counted from A?
No. It is counted from the first column of the selected table range. If the range starts at C, C is index 1, regardless of its worksheet letter.

What does column index number 1 return?
It returns a value from the first column in the table range—the same column VLOOKUP searches. An index of 2 returns the next column to its right.

Can the VLOOKUP column index be 0?
No. The index must be at least 1. Zero is commonly used as the fourth argument to request an exact match, but it is not a valid column index.

Why does VLOOKUP return #REF!?
Usually, the index asks for a column beyond the right edge of the selected range, or a referenced column was deleted. Check the range width and count from its leftmost column again.

Does VLOOKUP count hidden columns?
Yes. Hidden columns are still part of the table range and count toward the index. Hiding a column changes its display, not its position in the range.

Does VLOOKUP support a column to the left?
No. Its lookup column must be the leftmost column in the range, and it returns from that column or one to its right. Use XLOOKUP in supported versions or another lookup approach when the return column is on the left.

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.