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.

**Table of Contents**hide

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

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

`=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 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:

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

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:

`=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:

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.

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

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

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

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

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

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.