Md. Abdul Kader
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.
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.
