fbpx
Search
Close this search box.

Find first non-blank item in a list with formulas

Share

Facebook
Twitter
LinkedIn

Blank cells are an invisible pain in the analysis. Dealing with them is frustrating, time-consuming and often very complex. At chandoo.org, we are not big fans of blank cells. That is why we wrote:

Today, lets talk about one more scenario. Lets say you want to find out the first non-blank item in a list. How would you do it?

Lookup first non blank cell using Excel formulas

Finding first non-blank 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 non-blank 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:

  1. Select entire list
  2. Press F5, click on special
  3. Choose blanks, click ok.
  4. Press CTRL –
  5. Remove rows (or shift cells up as needed).
  6. Done!

Now that the blank cells are gone, just refer to B3 to get the first non-blank item in the list.

Using formulas

Although the non-formula 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 non-blank cell is a number (or date, % or Boolean value), the formula shows next cell that contains text.

How to find first non-blank value (text or number)?

If you want to find first non-blank 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 non-blank 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 non-blank 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.

  1. Can you think of some other formulas to find first non-blank cell?
  2. What formula gives 2nd non-blank cell value?
  3. What formula gives last non-blank 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.

 

The VLOOKUP Book - Definitive guide to Excel lookup functions & tricks
Comprehensive and easy to understand

This is a book for everyone who uses Vlookup. Most of us think… Oh.. I already know the function. But this book will open your eyes to some brilliant techniques. – By Dr. Nitin Paranjape

Solid introduction to lookup functions

This books does a wonderful job of taking each of the lookup functions available in Excel, breaking them down to a simple, easy-to-understand level. – by Lucas Moraga

Get your copy

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

24 Responses to “Find first non-blank item in a list with formulas”

  1. Rob T says:

    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 non-blank 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 non-blank value can be found by putting =MAX(C3:C100) into cell C1. Adding a -1 to this will result in the penultimate non-blank 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?

  2. JLeno says:

    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?

  3. Arun says:

    Answer to second home work question:

    {=INDEX(range,MATCH(FALSE,ISBLANK(range),0)+2)}

  4. Mark Duchesne says:

    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.

  5. Andre says:

    Also not a pretty one, but works quite well. this is the find the n'th non-blank entry in the column

    {=IFERROR(INDEX(range,SMALL(((range)<>"")*ROW(range),COUNTIF(range,"")+ROW())),"")}

  6. Harish Garg says:

    These are also looks easy.
    Please try this.

    Non-blank cell that contain number =INDEX(A1:A27,SMALL(IF(ISNUMBER(A1:A27),ROW(A1:A27)),1))

    Non-blank cell that contain text:
    =INDEX(A1:A27,SMALL(IF(ISTEXT(A1:A27),ROW(A1:A27)),1))

    Second non-blank cell:
    =INDEX(A1:A27,SMALL(IF((A1:A27)"",ROW(A1:A27)),2))

    Last non-blank cell:
    =INDEX(A1:A27,LARGE(IF((A1:A27)"",ROW(A1:A27)),1))

  7. Trouttrap2 says:

    Does this work if a number is the first non-blank in the series?
    =VLOOKUP("*",B3:B100,1,FALSE)

    • Trouttrap2 says:

      Never mind. I found the note:
      NOTE: This approach finds first cell that contains any TEXT. So if the first non-blank cell is a number (or date, % or Boolean value), the formula shows next cell that contains text.

  8. […] Find first non-blank item in a list with formulas […]

  9. John Waddell says:

    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.

  10. Avinash Shetty says:

    1) Can you think of some other formulas to find first non-blank cell?
    Ans: =INDEX(A1:A5,COUNTBLANK(A1:A5)+1)

    2) What formula gives 2nd non-blank cell value?
    Ans: Replace the value 1 from the above formula with 2nd 3rd whichever value you want.

  11. Avinash Shetty says:

    Here is the answer to the 3rd question...

    3) What formula gives last non-blank cell value?

    =INDEX(A1:A10,MAX(MATCH(9^9,A1:A10),MATCH(REPT("z",255),A1:A10)))

  12. henry says:

    I am looking for a way to retrieve the column heading for the first non-blank value (text or number) it finds in a row.

    Suppose I have the following
    1 Results: - Column B - Column C - Column D
    2 Column C - - 1 -
    3 Column B - 5 - -
    Do I use the same formula and add another function?

    thanks

  13. Cristian says:

    Hello,

    Please help me with this. I want to enter in a cell the fist non-blank value, going from right to left. For instance, I write my formula in cell J21, and the first non-blank value going from right to left in row 21 is in cell d21(a21, b21 and c21 also have some values inside). I want the function to return the same value that is in cell d21.

    How can I do it?
    Thank you

  14. JeteMc says:

    Awesome!!

  15. Jefferson says:

    Thanks, very good excel knowledge

  16. Reuben says:

    This is AWESOME for rows, but what if you need to do it for columns instead. say you have columns A:D(populated from another sheet), where any one of those columns could be completely blank. how could you modify this formula to do the same thing left to right for columns that it does top to bottom for rows?

  17. C.P. Oommen says:

    How can I convert chess pgn strings into excel cells as in the pgn given below.
    1. 1.e4 c5 2. Nf3 Nc6 3. d4 cd4 4. Nd4 Nf6 5. Nc3 d6 6. Bg5 e6 7. Qd2 a6 8. O-O-O h6 9. Be3 Be7 10. f3 Nd4 11. Bd4 b5 12. Kb1 Qa5 13. a3 Rb8 14. f4 b4 15. Bf6 Bf6 16. Qd6 Rb6 17. ab4 Qb4 18. Bb5 B62 SIC/B 66
    2. 1. e4 e5 2. Nc3 Nf6 3. Bc4 Nc6 4. d3 Na5 5. Nge2 Nc4 6. dc4 Bc5 7. O-O d6 8. Bg5 Be6 9. Nd5 Bd5 10. Bf6 Qf6 11. Qd5 O-O 12. Rab1 c6 13. Qd3 a5 14. Kh1 Rfe8 15. f3 Re6 16. Ng3 Qg5 17. Nf5 g6 18. Ng3 Qg3 C28 BLK

Leave a Reply