How to Drag Formula Horizontally with Vertical Reference in Excel

Sometimes you might need to drag the formula horizontally with the vertical reference. In other words, you have to utilize the Fill Handle Tool for columns with respect to vertical referenced data. In this guiding session, I’ll show you 3 methods with proper explanations to drag the formula horizontally with vertical reference in Excel.


Download Practice Workbook


3 Methods to Drag Formula Horizontally with Vertical Reference in Excel

Let’s say, you have a dataset like where the Sales Report of Fruits is given along with the Product ID, Sales Rep, Fruits, States, and Sales.

Dataset

Now, I’ll show you examples of how to drag the formula horizontally with vertical reference.


1. Using COLUMN Function with VLOOKUP

Firstly, you’ll show the use of the VLOOKUP function to extract specific data from the above dataset. In this, the COLUMN function is used as the argument to define the column number. For example, you may want to extract relevant data (Sales Rep, Fruits, States, and Sales) for the Product ID 1002.

Using COLUMN Function with VLOOKUP

So, the formula will be like the following in the C15 cell.

=VLOOKUP($B$15,$B$5:$E$12,COLUMN(C$4)-1,FALSE)

Here, $B$15 is the lookup value, $B$5:$E$12 is the table_array argument, C$4 refers to the Sales Rep heading of column C, and lastly FALSE is for the exact matching.

Formula Explanation:

  • In the above formula, the COLUMN(C$4) syntax returns column number as 3 but you need to subtract 1 as the Sales Rep column is located at the 2nd position with respect to the lookup column (Product ID).
  • Later, the VLOOKUP portion finds the relevant Sales Rep.

Excel Drag Formula Horizontally with Vertical Reference Using COLUMN Function with VLOOKUP

After pressing ENTER, you’ll get the output as James and drag the formula horizontally to pull out the other data for the specified Product ID.

Excel Drag Formula Horizontally with Vertical Reference Using COLUMN Function with VLOOKUP

Eventually, the output will be as follows.

Excel Drag Formula Horizontally with Vertical Reference Using COLUMN Function with VLOOKUP

If you look closely at the above picture, you’ll get that the extracted data are available in Row 6 of the dataset.

Read More: [Fixed!] Excel VLOOKUP Drag Down Not Working (11 Possible Solutions)


Similar Readings


2. Dragging Formula Horizontally with Vertical Reference Utilizing INDEX Function 

Subsequently, I changed the dataset a little bit to show you another example of dragging the formula horizontally what you are looking for!

In the updated dataset, I used 1002 Product ID multiple times to extract the corresponding Sales Rep from different rows.

Utilizing ROW and COLUMN Functions with INDEX Function

For doing that, the formula will be-

=INDEX($C$5:$C$12, SMALL(IF($B$15=$B$5:$B$12,ROW($B$5:$B$12)-ROW($B$5)+1), COLUMN(A1)))

Here, $B$5 refers to the starting cell of Product ID, $C$5:$C$12 is the cell range representing Sales Rep, and $B$5:$B$12 cells denotes Product ID.

Formula Explanation:

  • The ROW function mainly finds the row number from a given range of datasets. Here, ROW($B$5:$B$12)-ROW($B$5)+1 returns the row number of the $B$5:$B$12 cells used as the 2nd argument of the IF logical function.
  • Then, the IF($B$15=$B$5:$B$12,ROW($B$5:$B$12)-ROW($B$5)+1) executes searching to find the 1002 Product ID. When the formula matches, it returns TRUE, else it returns FALSE.
  • Basically, the SMALL function finds the smallest value in a given dataset. Here, the function returns the lowest row number which is assigned as the row_num argument in the INDEX function.
  • Lastly, the INDEX function extracts the first Sales Rep for 1002 Product ID.

Excel Drag Formula Horizontally with Vertical Reference Utilizing ROW and COLUMN Functions with INDEX Function

Therefore, drag the formula horizontally to get the rest of the Sales Rep of the 1002 Product ID.

Excel Drag Formula Horizontally with Vertical Reference Utilizing ROW and COLUMN Functions with INDEX Function

So, you’ll get the following output.

Excel Drag Formula Horizontally with Vertical Reference Utilizing ROW and COLUMN Functions with INDEX Function

Read More: How to Enable Drag Formula in Excel (With Quick Steps)


3. Applying ROW and COLUMN Functions with MIN Function

Similarly, you can extract the Sales Rep of Ohio state. In this section, I’ll modify the formula a bit to demonstrate to you another way.

Applying ROW and COLUMN Functions with MIN Function

Insert the following formula.

=INDEX($C$5:$C$12, SMALL(IF($C$14=$B$5:$B$12, ROW($B$5:$B$12)-MIN(ROW($B$5:$B$12))+1, ""), COLUMN(A1)))

Here, the MIN function returns the lowest row number from the given $B$5:$B$12 cells. And rests work similarly I’ve discussed in the earlier method.

Applying ROW and COLUMN Functions with MIN Function

Next, drag the formula horizontally.

Excel Drag Formula Horizontally with Vertical Reference Applying ROW and COLUMN Functions with MIN Function

Finally, you’ll get the following output.

Excel Drag Formula Horizontally with Vertical Reference Applying ROW and COLUMN Functions with MIN Function

Read More: How to Drag Formula in Excel with Keyboard (7 Easy Methods)


Conclusion

This is how you can drag the formula horizontally with vertical reference in Excel. I hope this article will be beneficial for you. Anyway, don’t forget to share your thoughts in the comment section below.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo