If a VLOOKUP column index number is not working, check that it is a positive number no larger than the number of columns in the table range, and count from the range’s first column—not from column A on the worksheet. Then confirm the range starts with the lookup column and use FALSE for an exact match unless you specifically need an approximate match.
What the column index number means
VLOOKUP searches for a value in the leftmost column of a selected range, then returns a value from another column in the same row. Its column index number, also called col_index_num, tells Excel which column within that selected range to return. It does not identify a worksheet column by its letter or number.
For example, if the table range is A2:D100, the index numbers refer to A, B, C, and D as 1, 2, 3, and 4. If the range instead starts at B2, then B is index 1, C is index 2, and D is index 3. This distinction is the source of many formulas that return a plausible but incorrect value.
The full VLOOKUP argument order is lookup value, table range, column index number, and an optional match setting. In Microsoft Excel for Microsoft 365 on Windows, an explicit exact-match setting is usually safer than relying on the optional argument. The index number is always measured from the left edge of the table range supplied in the formula.
Check the range and index before changing the formula
First select the formula cell and inspect the formula bar. Identify the table range, then count its columns from left to right. If the index is 4 but the range contains only three columns, the requested return column is outside the range. If the index is 2 but you meant the second worksheet column, remember that the index refers to the second column of the selected range instead.
Use this quick checklist:
- Is the lookup column the first column in the table range? VLOOKUP cannot search a column to the right and return a value from a column to its left.
- Does the index start at 1? An index of 0 or a negative number is invalid.
- Is the index no greater than the range’s column count? A range from B:D has three columns, not four.
- Does the selected range include the column you want to return?
- Is the formula using the intended match mode, usually FALSE for exact matching?
After correcting the formula, test it with a lookup value whose matching row and expected return value you can identify. That makes it easier to tell whether the problem was the index, the lookup range, or the match behavior.
Common errors and what they indicate
A #REF! error often means the index asks for a column beyond the right edge of the table range. For example, an index of 5 cannot return a value from a four-column range. Expand the range to include the intended column or lower the index if it was counted incorrectly.
A #VALUE! error can appear when the column index is less than 1, such as 0. The index should be a positive whole number. If the index is calculated by another formula, check that calculation too; a blank or unexpected input can lead to an invalid result.
If VLOOKUP returns a value but it is from the wrong field, the index is usually valid but points to the wrong column. A common cause is counting worksheet letters instead of columns within the table range. Another is inserting a column in the middle of a referenced range: a hard-coded index may still be a valid number yet now refer to a different field.
If the result is #N/A, the index may not be the problem. Excel may not find the lookup value in the first column, or the values may differ in a way that is hard to see. For example, one cell may contain a number stored as text, or a text key may have extra spaces. With approximate matching, an unsorted first column can also produce an unexpected match. Check the lookup value and match setting before changing the index.
A worked example with a reliable exact match
Suppose A1:D4 contains a product table with headers Product ID, Name, Category, and Price. The data rows are 101, Mug, Kitchen, 8.50; 102, Beans, Grocery, 19.99; and 103, Filter, Coffee, 6.25. If cell F2 contains 102 and the lookup range is A2:D4, the third column in that range is Category. With the lookup value in F2 and the table in A2:D4, use =VLOOKUP(F2,$A$2:$D$4,3,FALSE). The result is Grocery.
The dollar signs keep the table range fixed if you copy the formula to other cells. They do not change which columns the index counts. To return the Price for the same product, the index is 4 because Price is the fourth column of A2:D4. With the same layout, use =VLOOKUP(F2,$A$2:$D$4,4,FALSE). The result is 19.99.
Notice that the range begins on row 2, where the data starts. Including the header row would not change the column count, but it is not necessary for this lookup. The essential requirement is that the first column in the selected range contains the product IDs and that every requested return column is included.
Make the index respond to a selected header
A fixed number is simple, but it can be fragile when columns are rearranged or when a formula needs to return different fields. One option is to calculate the index by matching a chosen header against the table’s header row. In this layout, A1:D1 contains the headers, A2:D100 contains records, F2 contains the product ID to find, and G1 contains the return header, such as Price. Use =VLOOKUP(F2,$A$2:$D$100,MATCH(G1,$A$1:$D$1,0),FALSE). If G1 is Price, MATCH returns 4, so VLOOKUP returns the Price value for the matching product.
This approach helps when users choose a return field from a list, but it depends on the header text matching exactly. A misspelled header or an extra space can make MATCH return #N/A. Keep the header row and data range aligned, and make sure the selected header belongs to the same table. If columns are inserted, the header search can adjust the index automatically, provided the relevant ranges are updated to include the changed table.
Exact match, approximate match, and misleading results
The fourth VLOOKUP argument controls how Excel matches the lookup value. FALSE requests an exact match. TRUE, or leaving the argument out, requests an approximate match; for that mode, the first column should be sorted in ascending order for predictable results. Approximate matching is useful for bracketed values such as rate bands, but it is not a substitute for an exact product ID or employee number search.
Match mode and column index solve different problems. FALSE does not correct an index that points to the wrong return column, and a correct index does not make an approximate lookup exact. If a formula returns a believable but incorrect answer, verify both the selected column and the match setting rather than assuming the lookup worked because it did not show an error.
For an exact match that returns #N/A, compare the lookup value with the first-column entries. Check whether numbers are stored as text, whether identifiers have leading zeros, and whether text includes unwanted spaces. Do not remove leading zeros from codes if they are meaningful: an identifier such as 00125 is not necessarily interchangeable with 125.
When another lookup method is a better fit
VLOOKUP remains useful for straightforward tables, but its left-to-right design and numeric index can make formulas harder to maintain. In Microsoft 365, XLOOKUP can name the lookup and return ranges separately. It can also search in either direction, so the return column does not have to sit to the right of the lookup column.
With product IDs in A2:A100, prices in D2:D100, and the ID to find in F2, use =XLOOKUP(F2,A2:A100,D2:D100,"Not found"). The formula returns the matching price, or Not found if there is no match. XLOOKUP is available in Microsoft 365 and Excel 2021; it is not available in older perpetual Excel versions such as Excel 2019. If a workbook must work in an older version, VLOOKUP with a correctly counted index may be more compatible.
Another practical option is to keep VLOOKUP but use a header-based index, as shown above. Choose based on who will open the workbook, whether columns may move, and whether the lookup column is to the left of the result. For a stable, simple table, a fixed index can be clear. For a changing layout, XLOOKUP or a header-driven approach can reduce the chance of returning the wrong field.
Frequently Asked Questions
How do I count the VLOOKUP column index number? Count columns from the first column of the table range in the formula, starting at 1. If the range is C2:F50, C is 1, D is 2, E is 3, and F is 4.
Why does my VLOOKUP return the wrong column? The index is probably counting from the wrong starting point, or the table layout changed after the formula was written. Count within the current table range and compare the selected column with the field you intend to return.
What does #REF! mean in a VLOOKUP? A common cause is an index larger than the number of columns in the selected range. Expand the range to include the result column or correct the index so it falls within the range.
Can the VLOOKUP column index be 0? No. The index must be a positive number starting at 1. Zero is invalid; use 1 for the first column in the table range.
Why does VLOOKUP show #N/A even when the column index is correct? The lookup value may not exactly match anything in the first column, or the values may use different types or contain extra spaces. For an exact lookup, use FALSE and inspect the key values as well as the index.