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))

Search JabSto ::

Custom Search