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.

Sample Data

Syntax of the VLOOKUP Function:

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

Sample Data

  • 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”.

VLOOKUP by Using Column Index Number from Another Sheet

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

VLOOKUP by Using Column Index Number from Another Sheet

Step 1:

  • In cell D5, type the following formula.
=VLOOKUP(B5,Sheet1!$B$5:$D$11,3,FALSE)

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.

VLOOKUP by Using Column Index Number from Another Sheet

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

VLOOKUP by Using Column Index Number from Another Sheet

Step 2:

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

VLOOKUP by Using Column Index Number from Another Sheet

Step 3:

  • Type the following formula to find the price of a particular Order ID.
=VLOOKUP(B8, B5:D11,3)

VLOOKUP by Using Column Index Number from Another Sheet

  • As a result, you get the price according to the Order ID1412046”.

VLOOKUP by Using Column Index Number from Another Sheet

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

VLOOKUP by Using Column Index Number from Another Sheet

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

VLOOKUP by Using Column Index Number from Another Sheet

Step 1:

  • Firstly, type the VLOOKUP function and select the B5.
=VLOOKUP(B5,)

VLOOKUP by Using Column Index Number from Another Sheet

Step 2:

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

VLOOKUP by Using Column Index Number from Another Sheet

Step 3:

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

VLOOKUP by Using Column Index Number from Another Sheet

Step 4:

  • Press Enter to see the result.

Sample Data

Step 5:

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

Sample Data

Step 6:

  • Then, to look up a certain value, type the following formula in a cell.
=VLOOKUP(B8, B5:D11,3)

Sample Data

Step 7:

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

Sample Data

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

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo