fbpx

Lookup last non blank value – Excel Challenge

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

I have a fun Excel lookup challenge for you. You have data as shown below and want to find the last non blank value for a given account number. For example, for acct number 2015, the answer would be Freedom.

Lookup last non blank value for a given account number

How would you solve this?

Refer to this workbook for 3 possible answers. Just move the white box away to see the formulas.

If you have a different solution, post it in the comments section.

Fine print

  • Assume your data is in range A4:G13 with A4:A13 having the Account number and other columns containing some details.
  • The lookup value can be hardcoded or assumed be to in cell K3.
  • Click here for sample data file. This file also has some hints and 3 possible solutions.
  • You can use any Excel formula or Power Query or VBA based solution.
  • Post your answers in comments.

Happy solving.

Solution Video

Here is a video explaining 3 possible solutions to this problem. Watch it below or on my YouTube channel.

If you want more Excel challenges & homework problems, click here.

PS: Thanks Barbara for emailing me this question.

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
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.

21 Responses to “Lookup last non blank value – Excel Challenge”

  1. XLarium says:

    Hi Chandoo

    A Mix of old trick and new trick:
    =LOOKUP("?",FILTER(C4:E13,A4:A13=K3))

  2. Vit says:

    Don't have the =LET formula yet to remove the repetition.

    =INDEX(FILTER(A4:E13,(A4:A13=K3)*(D4:D13"")),ROWS(FILTER(A4:E13,(A4:A13=K3)*(D4:D13""))),4)

  3. Vit says:

    oh, I misunderstood the task lol. I interpreted "last" as in the last record (assuming there may be multiple records per account), not the last column in the respective record. my bad

  4. Jomili says:

    =LOOKUP(2,1/(A3:A13=K3),D3:D13) works for me.

    • David N says:

      Your solution will not work for all Acct Number values because you're only returning from column D as opposed to the last non-blank column for any account.

  5. Bill Szysz says:

    Hi Chandoo,
    I think that the real challenge would be to do this without dedicated lookup functions (no VLOOKUP, LOOKUP, X.LOOKUP, INDEX, MATCH, SEARCH, FIND etc) :-)))
    Of course, it is possible 😉

    • Chandoo says:

      Interesting ?

      One option I found is with TEXTJOIN and IF: =TEXTJOIN(,TRUE,IF(A4:A13=$K3,IF(ISBLANK(C4:F13),B4:E13&"",""),""))

      • Bill Szysz says:

        I wrote something similar to your solution but i assumed that there could be an empty cell(s) between levels (i.e. level_01 - Cash, Level_02 - empty cell, Level_03 - some text, Level_04 to 06 - empty cells)
        =TRIM(RIGHT(TEXTJOIN(REPT(" ",50),,IF((A4:A13=$K3),IF(ISTEXT(B4:G13),B4:G13,""),"")), 50))
        In the above, i also assumed that there are no longer than 50 characters text in each cell.
        Below, two other solutions without dedicated lookup/search functions.
        =TEXTJOIN(,,IF(A4:A13=K3,IFS(G4:G13"",G4:G13,F4:F13"",F4:F13,E4:E13"",E4:E13,D4:D13"",D4:D13,C4:C13"",C4:C13,B4:B13"",B4:B13),""))

        and

        =LET(rg, FILTER(B4:G13,A4:A13=K3),
        tbl, FILTER(rg, ISTEXT(rg)),
        cols, COLUMNS(tbl),
        FILTER(tbl,TRUNC(SEQUENCE(,cols,1/cols,1/cols))) )

  6. Chandan says:

    Dear Chandoo,

    I am looking for solution to one of the strange problem and was didnot fine any solution on internet may be i dont know the exact keywords to search it on net.

    I am trying to use pivot for my data where i have 3 columns, 1st column is text each unique value, in 2nd column date of 1st event happening all are different dates along 2001 to 2020; in 3rd column again dates of second event happening. In 3rd column there are certain entries where data is in first column with first event date but second event yet not happened. I want to use pivot table but unable to do so. can you help.
    like

    Person Date of filing documents date of issue of documents
    Maneesh 1 jan 2004 6 dec 2005
    John 15 may 2002 7 july 2009
    mira 16 april 2001 Not yet issued or/NA

    in this case first column data in pivot appears good and informative but in case of second column i am unable to find how many documents issued in 2001 and if am using filter all NA data all years get removed. can you help. Sorry for long explanation.

  7. Vaibhav Garg says:

    =LET(rng,VLOOKUP(K3,A3:E13,{2,3,4,5},FALSE),XLOOKUP(FALSE,ISBLANK(rng),(rng),,,-1))

  8. David N says:

    My solution was essentially the same as Chandoo's first answer, only using an Ω instead of REPT("z",5).

    I get the omega by using Alt+234 on the numeric keypad.

    =LOOKUP("Ω",INDEX(A3:E13,MATCH(K3,A3:A13,0),0))

    • David N says:

      Or for the last numeric value...
      =LOOKUP(99^9,INDEX(A3:E13,MATCH(K3,A3:A13,0),0))

      Or for the last non-blank regardless of text or numeric (and without the benefit of the new LET function to avoid repetition)...
      =LOOKUP(2,1/LEN(INDEX(A3:E13,MATCH(K3,A3:A13,0),0)),INDEX(A3:E13,MATCH(K3,A3:A13,0),0))

  9. Robert H. Gascon says:

    My formula is the same as that of David, except that I used "" instead of LEN, like this:
    =LOOKUP(2,1/
    (INDEX(B4:E13,MATCH(K3,A4:A13,0),0)""),
    INDEX(B4:E13,MATCH(K3,A4:A13,0),0))

  10. bosco_yip says:

    =LOOKUP("zzz",OFFSET(A1,MATCH(K3,A:A,0)-1,,,1000))

  11. sam says:

    Through PQ

    let
    Source = Excel.CurrentWorkbook(){[Name="D"]}[Content],
    mUnpivot = Table.UnpivotOtherColumns(Source, {"Acct Number"}, "Attribute", "Value"),
    mExtractLevelNo = Table.TransformColumns(mUnpivot, {{"Attribute", each Number.From(Text.AfterDelimiter(_, "_")), type number}}),
    mGroup = Table.Group(mExtractLevelNo, {"Acct Number"}, {{"Level", each Table.LastN(_,1)[Value]{0}, type text}})
    in
    mGroup

  12. Sanjit Patel says:

    I have one more solution. i hope you like it
    Creating extra column in your sheet.

    Formula in F4 is =COUNTA(A4:E4) and drag it down
    Formula in G4 is =VLOOKUP(A4,A3:E13,F4,FALSE) and drag it down

    now in L3, enter this formula and done.
    =VLOOKUP(K3,A3:G13,7,FALSE)

  13. GraH - Guido says:

    Also Power Query

    Load the "selection" as table to PQ. Drill down on value. Name the query "Selection".

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    List = Table.AddColumn(Source, "RecordAsList", each Record.ToList(Source{_})),
    Last = Table.AddColumn(List, "Last", each List.Last(List.RemoveNulls([RecordAsList]))),
    FilterSelection = Table.SelectRows(Last, each ([Acct Number] = Selection))
    in
    FilterSelection

Leave a Reply