Find first nonblank item in a list with formulas
Blank cells are an invisible pain in the analysis. Dealing with them is frustrating, timeconsuming and often very complex. At chandoo.org, we are not big fans of blank cells. That is why we wrote:
 How to delete blank cells & rows?
 Dealing with blanks – case study
 Quickly filling blank cells in a table
 Extracting nonblank data from a list
Today, lets talk about one more scenario. Lets say you want to find out the first nonblank item in a list. How would you do it?
Finding first nonblank item in a list
Lets say our list is in the range B3:B100.
Without using formulas
If you just want to get the first nonblank item in a list and do not want to use formulas, then you can remove all the blank items from the list. To do this:
 Select entire list
 Press F5, click on special
 Choose blanks, click ok.
 Press CTRL 
 Remove rows (or shift cells up as needed).
 Done!
Now that the blank cells are gone, just refer to B3 to get the first nonblank item in the list.
Using formulas
Although the nonformula approach works, it is manual. That means every time your data changes, you must repeat the steps. Not very cool, especially if you call yourself awesome. So, lets use a powerful formula to get that first non blank item in our list.
First see the formula:
=VLOOKUP("*", B3:B100, 1,FALSE)
How it works?
We want to lookup for first cell that contains something. It does not matter what that value is.
That is what * does. * is a wild card in Excel. When you ask VLOOKUP to find *, it finds the first cell that contains anything.
NOTE: This approach finds first cell that contains any TEXT. So if the first nonblank cell is a number (or date, % or Boolean value), the formula shows next cell that contains text.
How to find first nonblank value (text or number)?
If you want to find first nonblank value, whether it is text or number, then you can use below array formula.
=INDEX(B3:B100, MATCH(FALSE, ISBLANK(B3:B100), 0))
Make sure you press CTRL+Shift+Enter after typing this formula.
How this formula works?
ISBLANK(B3:B100) portion: This gives us list of TRUE / FALSE values depending on the 98 cells in B3:B100 are blank or not. It looks like this:
{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE; ...}
MATCH(FALSE, ISBLANK(…), 0) portion: Once we have the TRUE / FALSE values, we just need to find the first FALSE value (ie, first nonblank cell). That is what this MATCH function does. It finds an exact match of FALSE value in the list. (Related: Using MATCH Formula)
INDEX(B3:B100, MATCH(…)) portion: Once we know which cell is the first nonblank cell, we need its value. That is what INDEX does. (Related: Introduction to INDEX formula)
Home work for you
If you like this formula and want some challenge, read on.
For these home work problems, use the range B3:B100 or named range list in your formulas.
 Can you think of some other formulas to find first nonblank cell?
 What formula gives 2nd nonblank cell value?
 What formula gives last nonblank cell value?
Go ahead and post your answers using comments.
Drawing a blank when working on lookups?
If you are giving blank stares whenever your boss asks for lookup related stuff, then you are going to love this. My latest publication, The VLOOKUP Bookis a comprehensive guide to VLOOKUP, INDEX, MATCH, LOOKUP and other techniques to lookup any data and answer questions with confidence.
 
 

Leave a Reply
Quickly combine text in multiple cells using this trick! [Formulas]  Big trouble in little spreadsheet 
15 Responses to “Find first nonblank item in a list with formulas”
I know using helper columns is not very awesome, but in this instance I found a helper column very…. helpful:
In the helper column, the formula =COUNTA($B$3:$B3) in row 3, then copied down to row 100 gives the n we need in the “find the n’th nonblank value” challenge).
Then it’s simple to construct the formula (assuming helper column is C and we put the required n into C1):
=INDEX(B3:B100,MATCH(C1,C3:C100,0))
The last nonblank value can be found by putting =MAX(C3:C100) into cell C1. Adding a 1 to this will result in the penultimate nonblank value.
I tried to shoehorn the helper column into one array function using some form of $B$3:(B3:B100) but couldn’t find any way to make that work. Is there a way?
To find the nth nonblank value without a helper colum (array entered):
=INDEX($B$1:$B$100,SMALL(IF(NOT(ISBLANK($B$3:$B$100)),ROW($B$3:$B$100)),COUNTA(C$1:C2)+1))
The IF function returns FALSE for blank cells and the row number for all nonblank cells.
The SMALL function returns the nth lowest row number of nonblank cells, based on the number of nonblank values it has already found (using the COUNTA function).
The INDEX array must be based on the first row to work correctly, or otherwise include a workaround to find the right row number of the array.
Cheers
Any tidier suggestions?
Answer to second home work question:
{=INDEX(range,MATCH(FALSE,ISBLANK(range),0)+2)}
Answer to second home work question:
{=INDEX(range,MATCH(FALSE,ISBLANK(range),0)+1)}
my frustration over finding a cleaner method led me to the Microsoft support page:
{=INDEX(range,SMALL(IF(ISBLANK(range),"",ROW(range)),n)ROW(range)+1)}
Replace SMALL() with LARGE() if you wish to search from the end instead.
This solution also works if you replace the “ROW(range)+1″ part at the end with “1″
I am always amazed at how many different ways things can be worked out in excel
Not pretty i know but this is how i did it for the nth item.
Where D1 = the nth occurence
=INDEX(RANGE,(SUM(SMALL(IF(NOT(ISBLANK(RANGE))*ROW(RANGE)>0,NOT(ISBLANK(RANGE))*ROW(RANGE)),$D$1)))1,1)
Thanks Chandoo for the tips.
Also not a pretty one, but works quite well. this is the find the n’th nonblank entry in the column
{=IFERROR(INDEX(range,SMALL(((range)<>"")*ROW(range),COUNTIF(range,"")+ROW())),"")}
These are also looks easy.
Please try this.
Nonblank cell that contain number =INDEX(A1:A27,SMALL(IF(ISNUMBER(A1:A27),ROW(A1:A27)),1))
Nonblank cell that contain text:
=INDEX(A1:A27,SMALL(IF(ISTEXT(A1:A27),ROW(A1:A27)),1))
Second nonblank cell:
=INDEX(A1:A27,SMALL(IF((A1:A27)””,ROW(A1:A27)),2))
Last nonblank cell:
=INDEX(A1:A27,LARGE(IF((A1:A27)””,ROW(A1:A27)),1))
Does this work if a number is the first nonblank in the series?
=VLOOKUP(“*”,B3:B100,1,FALSE)
Never mind. I found the note:
NOTE: This approach finds first cell that contains any TEXT. So if the first nonblank cell is a number (or date, % or Boolean value), the formula shows next cell that contains text.
[…] Find first nonblank item in a list with formulas […]
I am looking for what should be a simple formula but cannot find it anywhere. I have several worksheets, each for a separate bank account.
All I wan to do is look up the “balance” column and print the latest balance in an account summary. i.e. look down the “balance” column until a blank cell is found, then report the value in the cell above (which will be the latest balance). Of course this changes each time a debit or credit is recorded therefore the formula needs to look for the first blank cell, then move up one cell to find the data.
Any suggestions? TY for any help.
1) Can you think of some other formulas to find first nonblank cell?
Ans: =INDEX(A1:A5,COUNTBLANK(A1:A5)+1)
2) What formula gives 2nd nonblank cell value?
Ans: Replace the value 1 from the above formula with 2nd 3rd whichever value you want.
Here is the answer to the 3rd question…
3) What formula gives last nonblank cell value?
=INDEX(A1:A10,MAX(MATCH(9^9,A1:A10),MATCH(REPT(“z”,255),A1:A10)))