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.

**Table of Contents**hide

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

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

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**, you’ll 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, 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**

**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, you’ll 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, I’ll 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 I’ve discussed in the earlier method.

Next, drag the formula horizontally.

Finally, you’ll 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, don’t forget to share your thoughts in the comment section below.