Find first non-blank cell in a range

Question

I am working with a list of data where one or multiple cells in a row can be blank.

Lets say the list is cells A1, A2, A3, A4. I am trying to create a function that will do the following:

IF A1 has a value I want the cell to return A1.
  IF A1 is empty then I want it to return A2.
   IF A1 and A2 are both empty I want it to return A3.
    If A1, A2 and A3 are all empty I want it to return A4.    

Answer 1

first result on google: http://chandoo.org/wp/2014/01/15/find-first-non-blank-item-in-a-list-excel-formulas/

This formula returns the first TEXT cell for a range B1:B100:

=VLOOKUP("*", B1:B100, 1,FALSE)

* is a wild card in Excel. When you ask VLOOKUP to find *, it finds the first cell that contains anything.

NOTE: This approach finds first cell that contains any TEXT. So if the first non-blank cell is a number (or date, % or Boolean value), the formula shows next cell that contains text.

If you need to find non-blank that url gives the following solution:

If you want to find first non-blank value, whether it is text or number, then you can use below array formula.

=INDEX(B1:B100, MATCH(FALSE, ISBLANK(B1:B100), 0))

Make sure you press CTRL+Shift+Enter after typing this formula.

How this formula works?

• ISBLANK(B1:B100) portion: This gives us list of TRUE / FALSE values depending on the 98 cells in B1:B100 are blank or not. It looks like this: {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE; ...}

• MATCH(FALSE, ISBLANK(…), 0) portion: Once we have the TRUE / FALSE values, we just need to find the first FALSE value (ie, first non-blank cell). That is what this MATCH function does. It finds an exact match of FALSE value in the list.

• INDEX(B1:B100, MATCH(…)) portion: Once we know which cell is the first non-blank cell, we need its value. That is what INDEX does.