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.

## 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 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.

**The Syntax:**

**=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])**

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.The column number in the table from which it will return.**col_index_num:**Required.**[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. The 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. 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.

## 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.

The formula we are using:

`=VLOOKUP(D14,B4:F12,5,FALSE)`

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 had given your column index number wrong, it would have given you a different value.

### 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:

The formula we are using:

`=VLOOKUP(D14,B4:F12,3,FALSE)`

Here, we wanted the Actor’s name from 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.

### 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:

`=VLOOKUP(D14,B4:F12,5,FALSE)`

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:

The formula we are using:

`=VLOOKUP(D14,B4:F12,6,FALSE)`

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.

### 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 this 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:

**=VLOOKUP($A1,table,COLUMN()-x,0)**

Let’s type the following formula in **Cell E5 **and press **Enter:**

`=VLOOKUP($C5,$H$5:$J$9,COLUMN()-3,0)`

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.

**Note:**

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 Columns **E** and** F**, the **COLUMN **function will return 5 and 6 respectively. In the sheet, column **E**‘s index number is 5. 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 the Name from the Student Database table (column 2), and the 2nd illustration obtains the 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**:

`=VLOOKUP(H4,B4:E9,MATCH(H5,B4:E4,0),FALSE)`

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**

**➤ MATCH(H5,B4:E4,0)**

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**.

`=VLOOKUP(C4,Actual!B4:F12,2,FALSE)`

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 column 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.

**Download Practice Workbook**

Download the following practice workbook.

## Conclusion

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.

Keep learning new methods and keep growing!

## Related Articles

- How to Use VLOOKUP for Multiple Columns in Excel
- VLOOKUP from Multiple Columns with Only One Return in Excel
- How to Use VLOOKUP to Return Multiple Columns in Excel
- How to Use VLOOKUP Function to Compare Two Lists in Excel
- How to Use the VLOOKUP Ascending Order in Excel
- VLOOKUP with Drop Down List in Excel
- How to Use VLOOKUP for Rows in Excel
- Perform VLOOKUP by Using Column Index Number from Another Sheet
- How to Find Column Index Number in Excel VLOOKUP

**<< Go Back to Excel VLOOKUP Function | Excel Functions | Learn Excel**

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.*”

Hello Thanks,

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.