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

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

Dataset

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.

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

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


  • [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)

Video liên quan