Find First Non Blank Cell in a Row

August 21, 2018

This tutorial will show you a powerful array formula to find the first non blank cell in a row in Excel.

In this example I have data in the cell range C2:K12. The formulas in column A finds the first non blank cell in each row.

 

The formula in cell A2 is:

=INDEX(C2:K2,1,MATCH(1,INDEX(1-ISBLANK(C2:K2),1,0),0))

Normally with array formulas you have to press Ctrl+Shift+Enter on your keyboard. The great thing about this array formula is that you don't need to do this.

 

 

How this Formula Works

 

The formula contains the INDEX, MATCH and ISBLANK functions. The INDEX function returns a value in an array when you specify the row or column number.

 

The MATCH function returns a row or column number of the value you are looking up in an array.

 

The ISBLANK function simply returns the Boolean values TRUE if the cell is empty and FALSE if it's not empty.

 

I will use the formula in cell A2 to explain how this works working from the inside out.

 

INDEX(1-ISBLANK(C2:K2),1,0)

This part of the formula looks at all the cells which are empty and not empty in the cell range C2:K2. It assigns a 1 if there is a value in a cell and 0 if there isn’t. The result returns the array {0,0,0,1,0,0,0,0,0}.

 

MATCH(1,INDEX(1-ISBLANK(C2:K2),1,0),0)

This section of the formula is saying that we want to lookup value 1 in the array {0,0,0,1,0,0,0,0,0}. The 1 in the array is in position 4 so the MATCH function will return 4.

 

INDEX(C2:K2,1,MATCH(1,INDEX(1-ISBLANK(C2:K2),1,0),0))

The INDEX function looks in the cell range C2:K2 and in the column argument of the INDEX function looks at the fourth column and returns the answer 8.

 

 

 

Final Word

 

I hope you have enjoyed this tutorial on how to find the first non blank cell in a row. If you have any questions or feedback on this tutorial then please leave a comment.

 

This cool formula is taken from a book that helps you solve 100 incredibly difficult Excel problems called Excel Gurus Gone Wild by Bill Jelen. You can see my review of this book by clicking here. You can also buy the book from my online shop or by clicking on the link below.

 

 

 

If you would like to see the true potential of Excel and how to create some truly magical formulas then you can download my workbook which contains 10 of the most powerful formulas with explanations of how they work. The best thing is that it is completely FREE!! Just return to the blog homepage and fill in your email address to receive a copy.

 

 

Excel Formulas and Functions: The Complete Excel Guide for Beginners Book

 

If you want to learn more on the INDEX and MATCH functions then you can buy my book called Excel Formulas and Functions: The Complete Excel Guide for Beginners here. This book shows you all the best Excel functions to use for your spreadsheets. This includes sum and count functions, lookup functions, logical functions and text functions.

 

 

Buy the Book

 

Please reload

Our Recent Posts

Please reload

Archive

Please reload

Tags

Please reload

  • Facebook Social Icon
  • Pinterest Social Icon
  • LinkedIn Social Icon
  • Google+ Social Icon
  • Twitter Social Icon

Excel Master Consultant

©2018 by Excel Master Consultant

This site was designed with the
.com
website builder. Create your website today.
Start Now