# Vlookup with Column Index Number from Another Sheet in Excel [4 Cases]

The column index number plays a vital role while extracting data in Excel using **Vertical Lookup (Vlookup).** You can refer to a column index number within the same sheet or a different sheet. It totally depends on where are you going to pull your data from. To give you a clear understanding of the topic, here I’m going to discuss **4 cases** to vlookup using the column index number from another sheet in Excel.

## Introduction to the Dataset

**Sheet1** has a datasheet of 3 columns. Which are **Employee ID, Department, & Salary.**

**Sheet2** also has a datasheet of 3 columns. The columns are **Employee ID, Department, and Salary.**

Here, I will show you how to extract the **Salary** amount from **Sheet1** to **Sheet2** based on the **Employee ID**s.

## What is Column Index Number in Vlookup?

**Column Index Number** is referred to as **Col_index_num** in Excel vlookup functions. It indicates the relative position number of a column in a certain range. You may want to specify the column index number in your vlookup formula to refer to a column that you want to extract from the output.

For example, we want to use the range **A2:D10** as the table array. In this range, there are 4 columns available. They are column **A** (range **A2:A10**), column **B** (range** B2:B10**), column **C** (range **C2:C10**), column **D** (range **D2:D10**).

Now when we use **1** as the column index number, it refers to column **A** (range **A2:A10**). Similarly, column index number **2** refers to column **B** (range **B2:B10**), **3** refers to column **C** (range **C2:C10**), and **4** refers to column **D** (range **D2:D10**).

### Case #1: Vlookup Using Column Index Number from Another Sheet within the Same Workbook

In **Sheet2**, I’ve 3 columns, **Employee ID, Department, and Salary.** The **Employee ID & Department** columns are filled up. But the **Salary** column is completely blank.

In **Sheet1**, there are 3 columns two, **Employee ID**, **Employee Name, & Salary.**

Now, I’m going to extract the salaries in the **Salary** column of **Sheet2** based on the **Employee ID**s from **Sheet1**.

For this purpose, I’m going to use the **VLOOKUP** function.

**Syntax**

=VLOOKUP (lookup_value, table_array, column_index_num, [range_lookup])

**Array Formula**

=VLOOKUP(A2:A10,Sheet1!$A$2:$C$10,3,FALSE)

**Usage Guide**

**Excel for Microsoft 365:** Insert the formula in cell **C2** and press **ENTER**. As it is an array formula, it will copy down itself from cell **C2** to **C10** automatically.

**For Excel 2019 and Previous Versions:** Press **CTRL + SHIFT + ENTER** to insert the formula into a cell instead of just pressing the **ENTER** button.

- The range
**A2:A10**is an array of lookup values. **$A$2:$C$10**is the table array. This is basically the range of the cells where the formula will look for the results.**‘Sheet1!’**is used before the table array range to mean that this table array range is located in**Sheet1**. If you don’t mention**‘Sheet1’**before the table array range, it will look for the results in the same worksheet.**3**is the column index number. This means the output is located in the**3rd**column of the table array range. The**3rd**column in the table array range**$A$2:$C$10**is column**C**. Which is the**Salary**column.- Finally
**FALSE**means the**VLOOKUP**function must look for the exact match between the lookup value array**A2:A10**and the table array range**$A$2:$C$10.**

**🔖 Reminder:** Make sure that the lookup column is the first column of the table array. Here, the lookup column is **A2:A10 **which is the first column of the table array **$A$2:$C$10.**

The previous one is an array formula. But if you don’t like array formulas, here’s the regular form of the previous formula for you.

**Regular Formula**

=VLOOKUP(A2,Sheet1!$A$2:$C$10,3,FALSE)

**Usage Guide**

Insert the formula in cell **C2**. Then drag down the bottom-right corner of cell **C2** up to cell **C10**.

**Final Result**

After copying down the formula, you will get the corresponding salaries against the **Employee ID**s in the **Salary** column.

### Case #2: Vlookup Using Column Index Number from Another Sheet from Another Workbook

In this case, Sheet1 is located in the **‘Book1.xlsx’** workbook. **Sheet2** is located in the **‘Book2.xlsx’** workbook.

To vlookup for salaries from **‘Sheet1, Book1.xlsx’ **to **‘Sheet2,Book2.xlsx’** use the following formula using the **VLOOKUP** function.

**Syntax**

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

**Array Formula**

=VLOOKUP(A2:A10,[Book1.xlsx]Sheet1!$A$2:$C$10,3,FALSE)

**Usage Guide**

**Excel for Microsoft 365:** Insert the formula in cell **C2** and press **ENTER**. As it is an array formula, it will copy down itself from cell **C2** to **C10** automatically.

**For Excel 2019 and Previous Versions:** Press **CTRL + SHIFT + ENTER** to insert the formula into a cell instead of just pressing the **ENTER** button.

**Formula Explanation**

**$A$2:$C$10**is the table array range.**[Book1.xlsx]Sheet1!$A$2:$C$10**means the table array range**$A$2:$C$10**is located in**Sheet1**in the workbook**‘Book1.xlsx’.**If you don’t mention the workbook name, the**VLOOKUP**function will consider the table array range is located in the same workbook.

**Click_here** to read how this formula actually works.

The previous one is an array formula. But if you don’t like array formulas, here’s the regular form of the previous formula for you.

**Regular Formula**

=VLOOKUP(A2,[Book1.xlsx]Sheet1!$A$2:$C$10,3,FALSE)

**Usage Guide**

Insert the above formula in cell **C2**. Then copy down the formula to the rest of the cell by dragging down the lower-right corner of cell **C2**.

After applying the formula all over the range **C2:C10**, in you get the corresponding salaries in the **Salary** column. Have a look.

### Case #3: Vlookup Using Column Index Number from Multiple Sheets of the Same Workbook

In **Sheet3** of the workbook** ‘Book1.xlsx’,** employee salaries are recorded against the **Employee ID**s from **1509014** to **1509020**.

In **Sheet4** of the workbook **‘Book1.xlsx’,** employee salaries are recorded against the **Employee ID**s from **1509021** to **1509027**.

Now I will show you how to vlookup from multiple sheets using the **Column Index Number**. I will combine these two data tables to create a complete list of salaries for **Employee ID 1509014 **to **1509027.**

To vlookup among multiple sheets, I’m going to use a formula with the **IFERROR** and **VLOOKUP** functions.

**Basic Formula Syntax**

=IFERROR(VLOOKUP(…), IFERROR(VLOOKUP(…), …, "No Data"))

**Regular Formula**

=IFERROR(VLOOKUP(A2, Sheet3!$A$2:$C$8, 3,FALSE), IFERROR(VLOOKUP(A2,Sheet4!$A$2:$C$8,3, FALSE), "No Data"))

**Usage Guide**

Insert the formula in cell **C2**. Then copy down the formula up to cell **C15** by dragging down the bottom right corner of cell **C2**.

**Formula Explanation**

The **VLOOKUP** function uses **A2** to look up vertically through **$A$2:$C$8** in both **Sheet3** and **Sheet4**. Then it returns the **3rd** column of the range **$A$2:$C$8** which is column **C**. The argument **FALSE** tells the **VLOOKUP** function that the matching between the lookup value **A2** and the table array range** $A$2:$C$8** has to be exact.

**🔖 Reminder:** Make sure that the lookup column is the first column of the table array. Here, the lookup column is **A2:A10 **which is the first column of the table array **$A$2:$C$10.**

The **IFERROR function** returns the extracted value by the **VLOOKUP** function. But if the **VLOOKUP** function cannot return any value, the **IFERROR** function returns the message **‘No Data’.**

**Final Result**

After copying down the vlookup formula, you will get the complete list of salaries for **Employee ID**s **1509014** to **1509027** in the **Salary** column.

If you are looking for the array formula version of the above vlookup formula, here you go.

**Array Formula**

=IFERROR(VLOOKUP(A2:A15, Sheet3!$A$2:$C$8, 3,FALSE), IFERROR(VLOOKUP(A2:A15,Sheet4!$A$2:$C$8,3, FALSE), "No Data"))

**Usage Guide**

**Excel for Microsoft 365:** Insert the formula in cell **C2 **and press **ENTER**. As it is an array formula, it will copy down itself from cell **C2 **to **C10 **automatically.

**For Excel 2019 and Previous Versions:** Press **CTRL + SHIFT + ENTER** to insert the formula into a cell instead of just pressing the **ENTER **button.

### Case #4: Vlookup Using Column Index Number from Multiple Sheets from a Different Workbook

In **Sheet3** of the workbook **‘Book1.xlsx’**, employee salaries are recorded against the **Employee ID**s from **1509014** to **1509020**.

In **Sheet4** of the workbook **‘Book1.xlsx’**, employee salaries are recorded against the **Employee ID**s from **1509021** to **1509027**.

Now I want to extract the salaries against the **Employee ID**s from **1509014** to **1509027**. I want to merge two salary lists from **Sheet3** & **Sheet4** of **‘Book1.xlsx’** and put them into **Sheet5** of **‘Book2.xlsx’.**

For this purpose, I’m using the following formula having the **IFERROR** and the **VLOOKUP** functions.

**Basic Formula Syntax**

IFERROR(VLOOKUP(…), IFERROR(VLOOKUP(…), …, "No Data"))

**Regular Formula**

=IFERROR(VLOOKUP(A2, [Book1.xlsx]Sheet3!$A$2:$C$8, 3,FALSE), IFERROR(VLOOKUP(A2,[Book1.xlsx]Sheet4!$A$2:$C$8,3, FALSE), "No Data"))

**Usage Guide**

Insert the formula in cell **C2**. Then copy down the formula up to cell **C15** by dragging down the bottom right corner of cell **C2**.

**Formula Explanation**

The **VLOOKUP** function uses **A2** to look up vertically through **$A$2:$C$8** in both **Sheet3** & **Sheet4** of **‘Book1.xlsx’.**

Then it returns the **3rd** column of the range **$A$2:$C$8** of **‘Book1.xlsx’** which is column **C**.

The argument **FALSE** tells the **VLOOKUP** function that the matching between the lookup value **A2** and the table array range **$A$2:$C$8** has to be exact.

**🔖 Reminder:** Make sure that the lookup column is the first column of the table array. Here, the lookup column is **A2:A10 **which is the first column of the table array **$A$2:$C$10.**

The **IFERROR** function returns the extracted value by the **VLOOKUP** function. But if the **VLOOKUP** function cannot return any value, the **IFERROR** function returns the message** ‘No Data’.**

**Final Result**

After copying down the vlookup formula, you will get the complete list of salaries for **Employee ID**s **1509014** to **1509027** in the **Salary** column.

If you are looking for the array formula version of the above vlookup formula, here you go.

**Array Formula**

=IFERROR(VLOOKUP(A2:A15, [Book1.xlsx]Sheet3!$A$2:$C$8, 3,FALSE), IFERROR(VLOOKUP(A2:A15[Book1.xlsx]Sheet4!$A$2:$C$8,3, FALSE), "No Data"))

**Usage Guide**

**Excel for Microsoft 365:** Insert the formula in cell **C2 **and press **ENTER**. As it is an array formula, it will copy down itself from cell **C2 **to **C10 **automatically.

**For Excel 2019 and Previous Versions:** Press **CTRL + SHIFT + ENTER** to insert the formula into a cell instead of just pressing the **ENTER **button.

## Conclusion

I tried to help you to vlookup from another sheet in Excel using a column index number. I expect you’ve found this article useful. You can read more articles relating to Excel from the Blog page of our website. Have a fantastic workday!