# How to Use VLOOKUP with a Column Index Number to Find Values from Another Sheet (2 Methods)

## Dataset Overview

In the screenshot below, you’ll find a sample dataset that we’ll use to demonstrate the two methods.

## Syntax of the VLOOKUP Function

The VLOOKUP function has the following syntax:

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

• Lookup_value: The value you want to look up (can be text or a number).
• Table_array: The range where the lookup value is searched.
• Col_index_num: The column number from which you want to retrieve the result.
• Range_lookup: An optional argument. Use FALSE for an exact match and TRUE for an approximate match.

### Method 1 – Using the Column Index Number to Look Up Values from Another Sheet

In the first image below, the dataset has three columns in Sheet1.

In the second image, the dataset is in another sheet, Sheet2. We want to look up values in this sheet from the Sheet1.

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

Here,

• D5 is the lookup_value.
• Sheet1!\$B\$5:\$D\$11 refers to the table array in Sheet1.
• 3 represents the column index number (col_index_num).
• FALSE ensures an exact match. This will give you the first lookup value from Sheet1 in cell D5.

• You will obtain the first look_up value from Sheet1 in cell D5.

• Use the AutoFill handle tool to fill the remaining cells.

• To find the price for a specific Order ID (e.g., 1412046), insert the formula:
`=VLOOKUP(B8, B5:D11,3)`

• You’ll get the price for Order ID 1412046.

### Method 2 – Looking Up Values from Another Workbook

Suppose the sample dataset is in another workbook called Previous Data.

We will apply the VLOOKUP function in our current worksheet Vlookup Index Another Sheet.

• In your current worksheet (Vlookup Index Another Sheet), follow these steps:
• Enter the VLOOKUP function and select cell B5:
`=VLOOKUP(B5,)`

• Go back to the Previous Data sheet.
• Select the range B5:D11 as the table array.

• Specify as the column index number.
• Use FALSE for an exact match.

• Press Enter to see the result.

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

• For a specific Order ID, insert the following formula in a cell:
`=VLOOKUP(B8, B5:D11,3)`

• Press Enter to see the result for the required Order ID.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF