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:

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:

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

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

• • Specify 3 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:

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

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

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