I have a problem that is described in the picture below. Until today, I have done manual changes to the formula and that will take time if I have massive data.

Anyone can help? I want to fix B7 and the next cell if I found another gap.

This is what i use: This is what i use

  • Can you please edit your question to 1) add the formula you are currently using (as text) 2) let us know exactly what the expected output is Nov 22 '18 at 5:09
  • Can you not remove that row?
    – Dave
    Nov 22 '18 at 6:43

You can utilize the LOOKUP formula to find the next non-empty value from above. For example, in row 7, column B (like in your example), enter


This will find the next non-empty value in the area above B7. I have placed the $ characters intentionally for copying the formula down the column.

(detailed explanation of the formular here)

If you have several thousands of rows, this may become a little bit slow, since the above formula leads to quadratic run time behaviour. If thats the case, but there is a known limit for the size of your gaps (for example, 2 rows), you can restrict the lookup to this specific number of rows, like


This checks only the three rows above B7 for valid values.

  • sorry for slow respond, i will test it now
    – Grideas
    Nov 29 '18 at 7:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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