Excel Tips and Tricks

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!

Ebook Download
View all
Learn
View all