I'm trying to create a formula so that Column E will output the Employee names on Column A. I've provided an image below as my example. As you can see, there is a pattern of 4 vacant after every employee's name.

enter image description here

I've tried using vlookup to no avail. Been working on offset for more than an hour now but it seems that I want the opposite of how the offset works like this

  • Hi! I'm trying to understand what you're trying to do? Should the "Vacant" be editable? Is the list of employee names the only dataset that needs to be taken into account?
    – David Tan
    Commented Apr 30, 2022 at 5:42
  • @DavidTan thanks for taking the time on my post. The vacant part is employee info which is fixed. What I'm trying to do is to re-organize various information and make it printer-friendly so the user can print the google sheet with various info of employees quickly. Commented May 1, 2022 at 7:55

1 Answer 1


I'm not sure whether this answer your question, but I think this should do it. https://docs.google.com/spreadsheets/d/1QZJODzDyA8RgM_Skh0Kor7O1r4-7dtmU1eROtAfIwzA/edit?usp=sharing

=IF(MOD(ROW()-1,5) = 0,OFFSET($A$1,CEILING(ROW()/5),0),"Vacant")

This is the function column E. Though it still needs to be adjusted since I see that your data starts at row 47. I'm not exactly sure on how this works, but it does.

=IF(MOD(ROW()-2,5) = 0,OFFSET($A$47,CEILING(ROW()/5)-9,0),"Vacant")

Note: You will need to use the fill handle because an array formula won't work. For some reason, this method of selecting then using the fill handle seems to work. It auto-increments the Employee number even though there isn't a pattern established.

enter image description here

  • Beautifully done! Thanks a lot!! Commented May 2, 2022 at 6:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.