Perform VLOOKUP by Using Column Index Number from Another Sheet

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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 ID “1412046”.

VLOOKUP by Using Column Index Number from Another Sheet


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

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


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


<< Go Back to VLOOKUP Between Worksheets | Excel VLOOKUP Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo