If you want to know how to use column index number in VLOOKUP in Excel, you’ve come to the right place.
Vertical Lookup, or VLOOKUP, is one of the most popular formulas used in Excel. While the formula’s syntax is simple, it may seem daunting to beginners. Today, we will explain the column index number in the VLOOKUP formula through various examples.
By the end of this tutorial, you will be able to identify the column index number to input in your VLOOKUP formula or enter the column index number through some functions.
Using the column index number in VLOOKUP
- Scenario on hand: We have a fictional dataset of Hollywood movie names with their year of release, name, male lead name, female lead name, and IMDB rating.
- What we want to accomplish: We want to understand how to use the column index number in VLOOKUP.
We will first understand the VLOOKUP syntax, then go through 3 methods to enter the column index number in the formula:
- Adding the column index number manually
- Using the column function to add the column index number
- Using the MATCH function to add the column index number
Understanding the VLOOKUP syntax
When you enter VLOOKUP in an Excel cell, you will get this prompt:
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Here is a little description of each of the values this formula requires:
- Lookup value: This is the value that the formula will search through the table.
- Table array: The table on which the lookup value will be matched.
- Column index number: The column number from which to return the value.
- Range lookup: False for exact matching, True for approximate matching.
As an example, here’s a VLOOKUP formula applied to our sample dataset of movies:
Here, we have labeled the column numbers from left to right.
In a separate cell, we entered the number “5” and applied a VLOOKUP formula to search for the number in the table.
This is the formula:
=VLOOKUP(I5,A6:F14,3,FALSE)
Here’s what we added to the VLOOKUP syntax:
- Lookup Value: 5 from the cell I5.
- Table Array: The entire data table from A6 to F14.
Note: We didn’t include the data labels here.
- Column Index Number: We wanted the movie name on row 5. The column index number for fetching this data was “3.”
- Range Lookup: We input False for the exact matching of 5 on the table.
This example makes VLOOKUP seem easy. But we can imagine your frustration getting an #N/A or #REF result. Even a tiny error in the VLOOKUP formula will result in no result.
Here are three common errors that cause these results:
VLOOKUP error 1 – Column number left to the lookup value
The first and most common error beginners face is an #N/A value despite the value being in the table.
To understand this error, you should know that VLOOKUP searches for text from the first column. So, if the value you are searching for is not on the first column and you want to fetch a value at index 2, VLOOKUP will yield a #N/A result.
Here’s an example to illustrate this better:
We enter “Ryan Adams” as the Lookup value in the VLOOKUP formula.
We give the correct table range and the correct column index number.
Still, the formula yields a #N/A value.
The reason?
The result column precedes the lookup value column.
The formula doesn’t detect “Ryan Adams” in the first column.
Even though the Column Index Number – 3 is entered correctly, the formula cannot go right to left to fetch the value.
Let’s swap the column order and change the formula a bit.
We now swapped the “Male Lead” and “Movie Name” column and input this formula:
=VLOOKUP(I5,C6:F14,2, FALSE)
Here, we have changed the table array so the lookup value column is at index 1.
Consequently, the index number for the Movie name changes to 2, and we get a result.
VLOOKUP error 2 – Out of range column index
Another common error that users face is an out-of-range column index.
Consider this example:
We want to calculate the average IMDB movie ratings when a certain male lead stars in the movie.
As a rule, we select the male lead column as the first column of the table array.
Instead of adding 3 to the formula for fetching the IMDB rating, we mistakenly add 4 and get a #REF error:
VLOOKUP error 3 – Wrong column index
When you enter the wrong column index number, the formula will fetch a value you don’t need.
You can choose one of the two methods we discuss in the next section to fetch the column number for these errors automatically.
How you can add the column index number in VLOOKUP
There are three methods to enter the column index number in a VLOOKUP formula:
Method 1 – Adding the column index number manually
The first method is simple. You add the table range with the lookup value in the first column.
Then, you count the column number from left to right and enter the column number from which you want to fetch the value.
For instance, in this image, you see that the first column is “Male Lead,” and the following columns are 2, 3, and 4.
To fetch the movie name, we entered the column number according to the table array:
Method 2 – Using the column function to add the column index number
The second method uses a column function to fetch the column index number.
This method is helpful for people handling a vast dataset.
Here’s the formula we enter for this to work:
=VLOOKUP({Lookup value}, {Table array}, COLUMN({Cell of last column in the range}-COLUMN({Cell of the second last column})+{One more than the number of columns in between lookup value column and fetch value column},FALSE)
=VLOOKUP(I5,C6:F14,COLUMN(F6)-COLUMN(E6)+3,FALSE)
Method 3 – Using the MATCH function to add the column index number
While the Column function can fetch the column value, there is a high chance of error when using this function.
A simpler way to fetch a column value is by using the MATCH function.
Here, instead of entering a column index number, we add the MATCH function, which prompts the formula to search for a column name.
Here’s the complete formula:
=VLOOKUP({Lookup value}, {Table array},MATCH({Column name},{Table label range}, 0),FALSE)
=VLOOKUP(I5,C5:F14,MATCH(H6,C5:F5,0),FALSE)
Here, if we change “IMDB Rating” to “Male Lead” the same formula will fetch the Male lead for the film entered as the lookup value:
Conclusion
VLOOKUP is an incredibly helpful formula that you can use for data handling. Even though this formula is simple, understanding how it works is necessary to yield its benefits. In this tutorial, we explored the column index number in the VLOOKUP formula and how you can fetch it automatically using two functions: COLUMN and MATCH.
Learn more about the VLOOKUP function in Excel through these guides:
