Microsoft Office Tutorials and References

In Depth Information

**FIND THE FIRST NON-BLANK VALUE IN A ROW**

Find the First Non-Blank

Value in a Row

Challenge:
You want to build a formula to return the ﬁ rst non-blank cell in a row.

Perhaps columns B:K reﬂ ect data at various points in time. Due to the sampling

methodology, certain items are checked infrequently.

Solution:
In Figure 1, the formula in A4 is:

=INDEX(C4:K4,1,MATCH(1,INDEX(1-ISBLANK(C4:K4),1,0),0))

Although this formula deals with an array of cells, it ultimately returns a single

value, so you do not need to use Ctrl+Shift+Enter when entering this formula.

Figure 1.
You i nd the i rst non-blank cell in each row of C2:K12 and return that value in

column A.

Breaking It Down:
Let’s start from the inside. The
ISBLANK
function returns

TRUE
when a cell is blank and
FALSE
when a cell is non-blank. Look at the row

of data in
C4:K4
. The
ISBLANK(C4:K4)
portion of the formula will return:

{TRUE,TRUE,FALSE,TRUE,TRUE,FALSE,TRUE,FALSE,TRUE}

Notice that this array is subtracted from 1. When you try to use
TRUE
and

FALSE
values in a mathematical formula, a
TRUE
value is treated as a 1, and

a
FALSE
value is treated as a 0. By specifying
1-ISBLANK(C4:K4)
, you can

convert the array of
TRUE/FALSE
values to 1s and 0s. Each
TRUE
value in the