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

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

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: **Excel Fill Down to Next Value

### 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 Drag Formula and Ignore Hidden Cells in Excel

### 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 Fill Formula Down to Specific Row in Excel

**Download Practice Workbook**

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

- How to Use Fill Handle in Excel
- How to Drag Cells in Excel Using Keyboard
- How to Drag Formula in Excel with Keyboard
- How to Enable Drag Formula in Excel
- [Solved]: Fill Handle Not Working in Excel
- How to Use Fill Handle to Copy Formula in Excel

**<< Go Back to Fill Handle in Excel | Learn Excel**