Perform VLOOKUP by Using Column Index Number from Another Sheet

The VLOOKUP function is used to look up values in a database. In most cases, we are used to applying the VLOOKUP function in the same sheet. However, we may need to refer to cells from another sheet or workbook to apply for a greater purpose. We’ll teach you how to use the VLOOKUP function using a column index number to find values from another sheet or workbook in this lesson.

VLOOKUP Column Index Number from Another Sheet: 2 Ways

In the sections below, firstly, we will show you how to use the VLOOKUP function to find values from another sheet. Then, we will apply the same to find values from another workbook additionally. In the screenshot below, we have provided a sample data set to do the task.

Syntax of the VLOOKUP Function:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]

• Lookup_value: It’s the value you want to lookup/find for. It can be a text or a number.
• Table_array: The range where the look_up value is searched.
• Col_index_num: It’s the number of the column in a selected range from where you want to get the result.
• Range_lookup: It’s an optional but important argument. ‘FALSE’ is applied to find the exact match, and ‘TRUE’ is applied to find the approximate match.

1. Apply the VLOOKUP Function to Lookup Values from Another Sheet by Using the Column Index Number

• In the first image below, the data set has three columns “Sheet1”.

• In the second image, the data set is in another sheet “Sheet2”. We now want to look up values in this sheet from the “Sheet1”. Follow the steps below to accomplish the task.

Step 1:

• In cell D5, type the following formula.

Here,

• D5 is the lookup_value.
• Sheet1!$B$5:$D$11 is the table_array reference with sheet 1.
• 3 is the column index number (col_index_num).
• FALSE is the argument for the exact match.

• Therefore, you will obtain the first look_up value from “Sheet1” in cell D5.

Step 2:

• Use the AutoFill handle tool to fill up the rest cells.

Step 3:

• Type the following formula to find the price of a particular Order ID.

• As a result, you get the price according to the Order ID “1412046”.

2. Utilize the VLOOKUP Function to Lookup Values from Another Workbook

In the section below, we will apply the VLOOKUP function to look up values from another workbook. In the image below, the data sample data set is in another workbook “Previous Data”.

• As shown in the screenshot below, we will apply the VLOOKUP function in our current worksheet “Vlookup Index Another Sheet”. Follow the instructions below to complete the task.

Step 1:

• Firstly, type the VLOOKUP function and select the B5.

Step 2:

• Go back to another sheet “Previous Data”.
• Select the range B5:D11 for table_array.

Step 3:

• Then type, 3 for the column index number (col_index_num).
• Write FALSE to do the exact match.

Step 4:

• Press Enter to see the result.

Step 5:

• To auto-fill, use the AutoFill handle tool.

Step 6:

• Then, to look up a certain value, type the following formula in a cell.

Step 7:

• Finally, press Enter to see the result for the required Order ID.

Conclusion

Finally, I hope you now understand how to use the VLOOKUP function to obtain values from another sheet. All of these tactics should be used while teaching and practicing with your data. Examine the practice book and put what you’ve learned into practice. Because of your valuable support, we are encouraged to continue offering initiatives like this.

Please do not hesitate to contact us if you have any queries. Please leave your opinions in the space below.

Stay with us and keep learning.

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
• How to Use Column Index Number Effectively in Excel VLOOKUP
• How to Find Column Index Number in Excel VLOOKUP