How to Use Column Index Number Effectively in Excel VLOOKUP

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:

Let’s discuss the arguments here briefly.

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:

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:

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:

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:

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:

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.

🔎 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).

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.

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:

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:

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