Perform VLOOKUP by Using Column Index Number from Another Sheet

The VLOOKUP function is used to lookup 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.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

2 Ways to Perform VLOOKUP by Using Column Index Number from Another Sheet

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, ‘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 is “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”.

Read More: How to Find Column Index Number in Excel (2 Methods)

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

In the section below, we will apply the VLOOKUP function to lookup 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.

Read More: How to Count Columns until Value Reached in Excel

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.

The Exceldemy team will respond as soon as possible.

Stay with us and keep learning.

Related Articles

• Excel VBA: Count Columns with Data (2 Examples)
• How to Convert Column Number to Letter in Excel (3 Ways)
• Count One Column If Another Column Meets Criteria in Excel (4 Suitable Ways)
• How to Count Columns for VLOOKUP in Excel (2 Methods)