# 4 Cases of Excel Vlookup with Column Index Number from Another Sheet

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.

Sheet1 has a datasheet of 3 columns. Which are Employee ID, Department, & Salary. Then, 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**).

## Vlookup Using Column Index Number from Another Sheet

Conducting a **VLOOKUP** using column index numbers from another sheet in Excel is a powerful technique that enhances data retrieval and analysis. This approach allows you to reference data across sheets by column index, offering flexibility and efficiency in managing large datasets. In this guide, we’ll explore how to leverage** VLOOKUP** with column index numbers, providing you with a dynamic method to extract and analyze information for 4 different cases.

### Case 1: 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)

**Formula Explanation**

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

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

To use **VLOOKUP** for column index number from another sheet, follow these steps below:

- Select an empty cell.
- Insert the formula:
**=VLOOKUP(A2,Sheet1!$A$2:$C$10,3,FALSE)** - Then, drag down the formula with
**Fill Handle**.

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

### Case 2: 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)

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

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

Now, apply VLOOKUP to use column index number from another workbook, go through these steps below:

- Select a blank cell.
- Write the formula:
**=VLOOKUP(A2,[Book1.xlsx]Sheet1!$A$2:$C$10,3,FALSE)** - Then, copy down the formula to the rest of the cell by dragging down the
**Fill Handle**icon.

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

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

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

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

- Select cell
**C2**. - Copy and paste the formula in the selected cell:
**=IFERROR(VLOOKUP(A2, Sheet3!$A$2:$C$8, 3,FALSE), IFERROR(VLOOKUP(A2,Sheet4!$A$2:$C$8,3, FALSE), “No Data”))** - Then, use
**Fill Handle**icon to drag down the formula to the column.

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"))

**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: From Multiple Sheets of 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"))

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

Follow the steps to use column index number from another workbook:

- Select cell
**C2**. - Insert the 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”))** - Then, copy down the formula up to cell
**C15**with**Fill Handle**.

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"))

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

## Frequently Asked Questions

### How to use column index number in Excel?

To use column index numbers in Excel, employ the **VLOOKUP** function. Specify the column index number as the second argument in the VLOOKUP formula, indicating the column from which to retrieve data. For example: **=VLOOKUP(lookup_value, table_array, column_index_number, [range_lookup])**

In this formula, ‘column_index_number’ represents the position of the desired data column within the ‘table_array.’ The lookup result will be retrieved from the specified column. This technique proves invaluable for dynamic data retrieval and analysis, allowing users to efficiently reference information across sheets or tables based on column index numbers.

### How do I give a column index number in VLOOKUP?

To assign a column index number in VLOOKUP, simply include it as the third argument in the formula. The syntax is as follows:** =VLOOKUP(lookup_value, table_array, column_index_number, [range_lookup])**

Specify the ‘column_index_number’ parameter to indicate the position of the desired data column within the ‘table_array.’ Excel will then retrieve information from the specified column based on the provided index number. This streamlined approach enhances data lookup processes and is particularly useful for referencing and analyzing information across different columns within your Excel workbook.

### How do I use VLOOKUP to get value from another sheet?

To use **VLOOKUP** to get a value from another sheet in Excel, include the sheet name and cell reference as part of the ‘table_array’ argument. The syntax is as follows: **=VLOOKUP(lookup_value, ‘Sheet2’!$A$1:$B$10, 2, FALSE)**

In this formula, replace ‘Sheet2’ with the actual name of the sheet containing the data. The ‘table_array’ now references cells on another sheet, and the VLOOKUP function will retrieve the value based on the specified lookup criteria. This technique streamlines data retrieval and analysis, enabling efficient cross-sheet referencing in your Excel workbooks.