VLOOKUP formula is one of the most useful formulas that are available in Excel. It is also one of the most frequent formulas you will stumble upon as well, especially when you begin to dig deep into the work with Excel.
In the example below, we will show how to copy the VLOOKUP formula down the column.
Copy a VLOOKUP Formula Down a Column
To show this in an example, we will create a table of different products and sales that a company achieved by selling those products:
In column D, we will list out the same products, but in a different order:
To get the sales results in column E, we will use a VLOOKUP formula, that has three mandatory parameters: 1) lookup_value (what we are searching for; 2) table_array (a table in which we are searching the value); 3) col_index_number (the value that we want to retrieve). This function also has a non-mandatory parameter, which is a 4) range_lookup (an approximate or exact value that will be shown).
In column E, we will insert the VLOOKUP formula, as follows:
|
1 |
=VLOOKUP(D2,A:B,2,0) |
To copy this formula throughout the column, we can use several options:
- Drag and drop the formula till the end of the table. We do this by clicking on the cell where the formula is, clicking on the bottom right corner, and dragging it. A similar picture as the one below will appear:
- We can also simply just click on the cross sign that will appear once we select a cell and then hover over it. When we click on it, our column will be populated.
- Once the process of filling is completed, we can click on the icon in the bottom-right corner, and then change the options of our fill (copy cells, fill formatting only, fill without formatting, or flash fill):
- We can also select the cell with the formula, and then select the range that we want to copy the formula to, and then go to the Home tab >> Editing >> Fill, and then choose the direction of our fill (in our case it will be Down direction):
Once we click on the Down option, we will have the column populated:
