Microsoft Office Tutorials and References

In Depth Information

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

ISBLANK
function changes to a 0. Each
FALSE
value changes to a 1. Thus,

the array becomes:

{0,0,1,0,0,1,0,1,0}

The formula fragment
1-ISBLANK(C4:K4)
speciﬁ es an array that is 1 row by

9 columns. However, you need Excel to expect an array, and it won’t expect

an array based on this formula fragment. Usually, the
INDEX
function returns a

single value, but if you specify 0 for the column parameter, the
INDEX
function

returns an array of values. The fragment
INDEX(1-ISBLANK(C4:K4),1,0)

asks for row 1 of the previous result to be returned as an array. Here’s the

result:

Part

I

{0,0,1,0,0,1,0,1,0}

The
MATCH
function looks for a certain value in a one-dimensional array and

returns the relative position of the ﬁ rst found value.
=MATCH(1,Array,0)

asks Excel to ﬁ nd the position number in the array that ﬁ rst contains a 1. The

MATCH
function is the piece of the formula that identiﬁ es which column contains

the ﬁ rst non-blank cell. When you ask the
MATCH
function to ﬁ nd the ﬁ rst 1 in

the array of 0s and 1s, it returns a 3 to indicate that the ﬁ rst non-blank cell in

C4:K4
occurs in the third cell, or
E4
:

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

Sub-result:
MATCH(1, {0,0,1,0,0,1,0,1,0},0
)

Result: 3

At this point, you know that the third column of
C4:K4
contains the ﬁ rst non-

blank value. From here, it is a simple matter of using an
INDEX
function to

return the value in that non-blank cell.
=INDEX(Array,1,3)
returns the value

from row 1, column 3 of an array:

Formula fragment:
=INDEX(C4:K4,1,MATCH(1,INDEX(1-ISBLANK(C4:

K4),1,0),0))

Sub-result:
=INDEX(C4:K4,1,3)

Result: 4

Additional Details:
If none of the cells are non-blank, the formula returns an

#N/A error
.

Alternate Strategy:
Subtracting the
ISBLANK
result from 1 does a good job

of converting
TRUE/FALSE
values to 0s and 1s. You could skip this step, but

then you would have to look for
FALSE
as the ﬁ rst argument of the
MATCH

function:

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