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

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

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.

🔖 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 IDs 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 IDs from 1509014 to 1509020.

In Sheet4 of the workbook ‘Book1.xlsx’, employee salaries are recorded against the Employee IDs 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 IDs 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 IDs from 1509014 to 1509020.

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

Now I want to extract the salaries against the Employee IDs 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 IDs 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!