Excel Fill Value Down Rows based on Criteria

• Share on Facebook.
• Share on Twitter.
• Share on Google+
• Share on LinkedIn
• Pin It!

A few weeks ago one of our members, Kylie, sent me this question:

“How can I fill column E with the text ‘CURRENT’, if the Position Title in column D matches the first instance of ‘CURRENT’ in column E?”

See example data below; Kylie wants to fill the cells bordered in orange:

Note: I had to desensitise Kylie’s data and the first names that popped into my mind were inspired by the French Open. I hope Federer likes his new position as Business Customer Rep, or is that Bus Customer Rep…either way it’s going to be a big change for him 🙂

Download the Workbook

Watch the Video

Fill Down based on Criteria

Unfortunately There's no IF option on the Fill tool but it's easy enough with a combination of Go To Special and an IF formula:

1. Select the cells in column E from the first row of your data to the bottom of your data e.g. E4:E4000, but don’t select the whole of column E, you just want the rows containing data.
2. Press CTRL+G to open the Go To dialog box > click the ‘Special’ button at the bottom:



3. Select ‘Blanks’ and click OK

This will have selected all blank cells in your data range in column E

4. In the formula bar enter this formula (assuming the first cell selected by Go To Special is cell E6, if not adjust cell references accordingly):

   =IF(D6=D5,E5,"")

Let’s translate the formula into English:

IF the Position Title in D6 is the same as the Position Title in D5, then insert the Current Flag from E5, otherwise leave the cell blank.

5. Press CTRL+ENTER to enter the formula in the selected empty cells



6. Copy column E and Paste as Values to get rid of the formulas.

Note: if it was possible for Kylie's data to have the Position Title repeated for different employees you could make the formula an IF(AND... like so:

=IF(AND(D3=D2, A3=A2),E2,"")

This would also ensure the CURRENT flag was only copied down to the relevant employee.

Please Share

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.