Excel Tips n Tricks -Tip 1 (Find the first
non-blank cell in a row)
Problem
You are given a task to create a formula to
return the first non-blank cell in a row. Here you can see Data Range D2:I9
shows data at various points for different dates.
Solution for the problem
![Image1.jpg]()
In cell A4 above picture the formula is
"=INDEX(D4:I4,1,MATCH(1,INDEX(1-ISBLANK(D4:I4),1,0),0))". It internally
deals with array of cells but ultimately returns a single value.
Formula Explained
Starting from inside, the ISBLANK function
returns a TRUE when a cell is blank and FALSE when a cell is non-blank. Hence,
the inner block "ISBLANK(D4:I4)" will return an array of True n FALSEs
like
{FALSE, TRUE, FALSE, TRUE, FALSE, TRUE}.
Moving forward you can see this array is subtracted from 1 means all the TRUE n
FALSEs in the array would be opposed as: {TRUE, FALSE, TRUE, FALSE, TRUE,
FALSE}.
The MATCH function looks for a certain value in a one-dimensional array and
returns the relative position of first found value. Moving Upward you see it
concludes to "=MATCH(1,array,0)".
In digital computation a TRUE means a 1 and a FALSE is 0. So, you can see this
formula fragment as
=MATCH(1,{1,0,1,0,1,0},0) (the last
parameter, 0 is the match type parameter)
You can easily have an idea that it will
return 1 as result since it matches 1 at the first position. At this point, you
know that the first column in range D4:I4 contains a non-blank value. Now going
higher in the main formula, it's just another Index function, see:
"=INDEX(array,1,1)" or "=INDEX(D4:I4,1,1)"
And the function above will return the first cell value in first column in data
range D4:I4 i.e. 4 and that's what we wanted to find. Rest is just drag down the
formula. Hope you enjoyed learning this trick. Feedback and suggestions are
welcome and highly appreciated. Follow for more!