How to apply vlookup to the entire column

By SpreadCheaters
Page last updated: 27/06/2023 | Next review date: 27/06/2025
close

Author bio


SpreadCheaters
View More north_east

When working with large datasets in Excel, it’s common to use the VLOOKUP function to retrieve specific information based on a lookup value. While VLOOKUP is a powerful tool, manually applying it to each cell in a column can be time-consuming and inefficient. We will understand how to apply VLOOKUP to an entire column in Excel, saving you valuable time and effort. Let’s dive in!

The dataset contains columns Product ID, Product Name, and Price. We need to find the Product Name based on their Product ID. This can be done by using the VLOOKUP on the entire column. The dataset is shown below.

Method – 1 Drag the formula.

Step – 1 Type the Formula.

  • Select the cell where the VLOOKUP value will be displayed.
  • Syntax of the formula:

VLOOKUP(lookup_value, table_array, col_index_num)

  • In our case formula will be:

=VLOOKUP(E2,A2:B11,2)

  • Hit Enter.

The formula explanation is given below.

Step – 2 Applying formula to the whole column.

  • Click on the cell that contains the formula.
  • Select the cell and drag it from the bottom right to the rest of the cells.
  • The VLOOKUP formula will be applied.

Method – 2 Using the Vlookup as Array formula.

Step – 1 Type the formula.

  • Select the range of cells where the VLOOKUP results will appear.
  • In the formula bar in the ribbon type the formula.
  • Syntax of the formula:

VLOOKUP(lookup_range, table_array, col_index_num)

  • In our case the formula will be:

=VLOOKUP(E2:E8,A2:B11,2)

Step – 2 Applying formula to the whole column.

  • Once you have typed the formula press Ctrl + Shift + Enter.
  • Ctrl + Shift + Enter is used when your formula is in array format.

Formula Explanation:

VLOOKUP(lookup_value, table_array, col_index_num)

Lookup_value: It is the value you want to lookup or search for. In our case it’s E2.

Table_array: This range should include the lookup and columns containing the data you want to retrieve. We used A2:B11 as table_array.

col_index_num: It is the column number in the table_array from which the matching value should be retrieved. The leftmost column is considered column number 1. We used 2.