In Microsoft Excel, VLOOKUP is one of the hottest topics that you will find on the internet. If I am not wrong, you have already seen numerous articles about the VLOOKUP formula in Excel with its functionality, usage, advantages, etc. If I start, I can discuss various aspects of the VLOOKUP. But, in this tutorial, I will only discuss the VLOOKUP column index number and its facts. It will be on point with suitable examples and proper illustrations. So, stay with us.
Download Practice Workbook
Download the following practice workbook.
Brief Description of the VLOOKUP Function
Before I start, let’s give you a quick recap of the VLOOKUP formula in Excel. I hope you can remember this function and its usage. You can skip this if you already remember everything about this function.
Now, the Excel VLOOKUP function peeks for a presented value in the leftmost column of a given table and produces a value in the exact row from a defined column.
Let’s discuss the arguments here briefly.
- lookup_value: Required. The value it searches for is in the leftmost column of the described table. It can be a single value or an array of values.
- table_array: The table where it will search for the lookup_value in the leftmost column.
- col_index_num: Required. The column number in the table from which it will return.
- [range_lookup]: It determines whether an exact or partial match of the lookup_value is needed. In the argument, 0 for an exact match, 1 for a partial match. Default is 1 (partial match).
Take a look at the following example:
In the example, you can see we have a movie dataset. Here, we used the VLOOKUP formula to extract data.
In this VLOOKUP formula:
Our lookup_value is 5 which is ID.
Our table array is B4:F12
Range lookup is set to FALSE for the Exact match.
The column index number is 5 which is Release Year.
Basically, we are telling the VLOOKUP function to search the table array with an ID 5. And if you found that then, give the value presented in the column Release Year which has a column index number of 5.
I hope you can understand now how it works.
Read More: Range Lookup with VLOOKUP in Excel (5 Examples)
6 Things You Should Know About Column Index Number of VLOOKUP
As the topic of this article is all about the VLOOKUP column index number, we will discuss this argument in the upcoming sections. The column index number is a crucial thing. To return a correct value from a table array, you should know some things about this column index number. I hope you will read all of these and apply them next time to your Excel sheet.
1. What Does Column Index Number Do?
Now, why column index number is essential, and what does it do in the formula. Let’s break it down from the previous example.
Formula we are using:
We set range lookup to FALSE for the exact match.
Breakdown 1: Lookup for a Value
Now, from the table, we want to search for something. To find that value, we must provide a lookup value in the VLOOKUP formula. First, we have to give the lookup value by which it will search the data. We are using cell reference here.
Breakdown 2: Initialize the Table Array
Then, the VLOOKUP function will look for the table array from where it will perform all of these.
Breakdown 3: Look for the Lookup value in the leftmost column
Now, VLOOKUP will search the lookup value first from the leftmost column of the table array vertically.
As we set the range lookup argument to FALSE to find an exact match, it will find one.
Breakdown 3: Look for the Column Index Number from Table Array
Now, the VLOOKUP function will try to find the column number from the table that you mentioned in the argument.
Breakdown 4: VLOOKUP will Look for Value in the Given Column Index Number
After it has found the desired column, VLOOKUP will search for your value in the column that shares the same row with ID 5.
Breakdown 6: Return the Value
Finally, it will find the Release Year with the ID 5.
As you can see, the column index number is the key to finding your desired value from the table. If you have given your column index number wrong, it would have given you a different value.
Read More: Lookup Value in a Range and Return in Excel (5 Easy Ways)
2. How Do Outputs Will Vary Based on Column Index Number?
Now, our VLOOKUP formula will return a different result based on the different column index numbers.
When you want an alternate result from the table, make sure you give the accurate column index number in the function. Your outputs will vary based on this column range.
Take a look at the following example:
Formula we are using:
Here, we wanted the Actor’s name from the Movie ID 5. Now, our Actor column is the 3rd column in the table array. For this reason, we provided 3 as a column index number in the VLOOKUP formula. As a result, our function fetched the value from range to lookup (the table array) in Excel.
Read More: How to Find Second Match with VLOOKUP in Excel (2 Simple Methods)
3. What Happens If We Give a Wrong Column Index Number?
Now, you may ask what will happen if we give the incorrect column index number using the Excel VLOOKUP function. Obviously, it will return a wrong value. If your desired result column doesn’t match the column index number you have given, you won’t get the result.
Take a look at the following example to have a better understanding:
The formula we are using:
Here, you can see we wanted the Genre of the Movie ID 7. But, it is showing 2006 which is an incorrect answer.
Because we provided 5 as a column index number in the Excel VLOOKUP formula. But the column number of Genre is 4. That’s why it is returning a different result.
4. What Happens If Our Column Index Is Out of Range?
If your column index number is out of range from the given table array, the VLOOKUP function will return the #REF! error. It is because your table array doesn’t contain that many columns.
You can understand this from the following example:
Formula we are using:
Here, you can see our Excel VLOOKUP formula is showing an error. Look closely, our table array is B4:F12. In this range, we have only five columns. But, in our formula, we provided 6 in the col index argument. That’s why it couldn’t return any value.
Read More: How to Use VLOOKUP to Find a Value That Falls Between a Range
5. Column Index Number Increase in VLOOKUP Using COLUMN Function
In the VLOOKUP formula, you can use the dynamic column index. Suppose you have two tables and you want to merge them. Also, you need to copy a working VLOOKUP formula in multiple columns. A handy way you can follow is- by using the COLUMN function of Excel.
Take a look at the following screenshot:
We have two tables here. We want to merge the Result table and Student Database table based on Student ID. Basically, our goal is to set the Name and section in the Result table.
The generic formula we are using:
Let’s type the following formula in Cell E5 and press Enter:
Now, drag the Fill handle icon across the table.
Again, type the formula in Cell F5 and drag the Fill handle icon:
As you can see, we have successfully copied the data from the student database to the Result table using the dynamic column index number.
You can use this formula as a Column Index Number Calculator for Vlookup in your worksheet.
🔎 Breakdown of the Formula
We calculated the column index number utilizing the COLUMN function. If there are no arguments in the COLUMN function, it returns the number of that related column.
Now, as we are using the formula in Column E and F, the COLUMN function will return 5 and 6 respectively. In the sheet, column E’s index number is 5. And column F’s index number is 6.
We don’t like to get data from the 5th column of the Result table (there are only 3 columns total). So, subtracted 3 from 5 to acquire the number 2, which the VLOOKUP function used to recover Name from the Student Database table.
COLUMN()-3 = 5-3 = 2 (Here, 2 indicates the Name column of the Student Database table array)
We used the same formula in Column F. Column F has index number 6.
COLUMN()-3 = 6-3 = 3 (3 indicates the Section of Student Database table array)
In the end, the first sample brings Name from the Student Database table (column 2), and the 2nd illustration obtains Section from the Student Database table (column 3).[/wpsm_box]
6. Find Column Index Number with MATCH Function
Now, in the VLOOKUP function, we usually give the column index number as a static number. Regardless, you can also form a dynamic column index number like the previous example. Here, we are doing this by using the MATCH function to find the required column. This method lets you make a dynamic two-way lookup, matching both rows and columns in the table array.
Take a look at the following dataset:
Here, we have a student dataset. We have some students’ grades in Physics, Math, and Chemistry. Now, we want to find the Grade of Jessy in the Math subject.
To find this, type the following formula in Cell H5 and press Enter:
As you can see, our VLOOKUP formula has gained the exact match from the dataset.
Now, look closely, we didn’t provide a static number in the column index number. Instead, we made it dynamic by using the MATCH function. Basically, we have multiple criteria here.
🔎 Breakdown of the Formula
The MATCH function will search for the subject MATH from the header B4:E4. It will find it in index 3. That’s why it will return 3.
➤ VLOOKUP(H4,B4:E9,MATCH(H5,B4:E4,0),FALSE) = VLOOKUP(H4,B4:E9,3, FALSE)
Finally, it will work like the actual VLOOKUP formula. And it will return C. Because it found the value from the column index number 3.
VLOOKUP Column Index Number from Another Sheet or Workbook
Now, you can use the column index number in a sheet that will fetch data from another sheet or another workbook. It is one of the important features of the VLOOKUP function.
Take a look at the following screenshots:
Here, we have two separate sheets. Our main dataset is in the Actual sheet. We will fetch the data from here and show it to the output sheet. Here, we are actually using a column index number where our table array is located on another sheet.
First, type the following formula in Cell C5 of the output sheet and then press Enter.
As you can see, our VLOOKUP column index number helped us fetch data from another sheet.
Here, the formula “Actual!B4:F12” basically indicates the sheet name and the table array from the sheet. And, we gave 2 as a column index number to fetch data from the table in the “Actual” sheet.
The same goes for the various workbooks. You can similarly fetch a value from another workbook using the VLOOKUP function. Take a look at the screenshots:
This is the first workbook. We have our data herein “Actual” sheet:
This is the second workbook. The value we will show in the “Data” sheet.
Like the previous one, type the following formula in Cell C5 of the “Data” sheet from “Other.xlsx” workbook:
=VLOOKUP(C4,'[VLOOKUP Column Index Number.xlsx]Actual'!B4:F12,2,FALSE)
As you can see, it returned an exact match from the dataset.
If you close the main workbook where your table array is, the formula will look like the following:
=VLOOKUP(C4,'D:\SOFTEKO\75- vlookup column index number\[VLOOKUP Column Index Number.xlsx]Actual'!B4:F12,2,FALSE)
The following screenshot will break it down for you:
💬 Things to Remember
✎ VLOOKUP searches value in the right direction.
✎ To get accurate results, provide a valid column index number. The column index number should not exceed the table array columns number.
✎ You should lock the table array range if you are conveying the data from the same worksheet or other worksheets but the exact workbook.
To conclude, I hope this tutorial has provided you with a piece of useful knowledge about the VLOOKUP column index number in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.
Keep learning new methods and keep growing!
This is useful. Thanks a lot.
I don’t understand why though, in the first 4 paragraphs, columns are indexed as such: column B’s index is 1, column C’s index is 2,…, column E’s index is 4 and column F’s index is 5″…
… But on the 5th paragraph “5. VLOOKUP Column Index Number Increase Using the COLUMN Function”, it is said that column E’s index is 5 and column F’s index is 6: “*Now, as we are using the formula in Column E and F, the COLUMN function will return 5 and 6 respectively. In the sheet, column E’s index number is 5. And column F’s index number is 6.*”
In the first 4 paragraphs, we use =VLOOKUP(D14,B4:F12,5,FALSE) this formula. Here, we define the range of cells. So, when we define column number as 5, Excel will count from column B as the first column.
Whereas, =VLOOKUP($C5,$H$5:$J$9,COLUMN()-3,0) in this equation, we use Column() function. This function will count the column number from column A. So, when you have COLUMN()-3 and are in column E, it will be 5-3= 2.
So, I hope you understand it clearly. If you have further queries, feel free to ask in the comment box.