# How do you drag a formula horizontally?

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

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, Ill show you 3 methods with proper explanations to drag the formula horizontally with vertical reference in Excel.

**Table of Contents** hideDownload Practice Workbook3 Methods to Drag Formula Horizontally with Vertical Reference in Excel1. Using COLUMN Function with VLOOKUP2. Dragging Formula Horizontally with Vertical Reference Utilizing INDEX Function3. Applying ROW and COLUMN Functions with MIN FunctionConclusionRelated Articles

## Download Practice Workbook

**Drag Formula Horizontally with Vertical Reference.xlsx**

## 3 Methods to Drag Formula Horizontally with Vertical Reference in Excel

Lets 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**.

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

### 1. Using COLUMN Function with VLOOKUP

Firstly, youll 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**.

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.

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

Eventually, the output will be as follows.

If you look closely at the above picture, youll 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**

**Drag Number Increase Not Working in Excel (A Solution with Easy Steps)****How to Fill Down to Last Row with Data in Excel (3 Quick Methods)****[Fixed!] Excel Drag to Fill Not Working (8 Possible Solutions)**

### 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.

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.

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

So, youll get the following output.

**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, Ill modify the formula a bit to demonstrate to you another way.

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 Ive discussed in the earlier method.

Next, drag the formula horizontally.

Finally, youll get the following output.

**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, dont forget to share your thoughts in the comment section below.

## Related Articles

**[Solved]: Fill Handle Not Working in Excel (5 Simple Solutions)****How to Drag Formula and Ignore Hidden Cells in Excel (2 Examples)****Fill Down to Next Value in Excel (5 Easy Methods)****How to Fill Formula Down to Specific Row in Excel (7 Easy Methods)**