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

Search JabSto ::

Custom Search