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.
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.
22 Responses to “Lookup last non blank value – Excel Challenge”
Hi Chandoo
A Mix of old trick and new trick:
=LOOKUP("?",FILTER(C4:E13,A4:A13=K3))
=INDEX(A4:E13;MATCH(K3;A4:A13;0);MAX(IF(INDEX(A4:G13;MATCH(K3;A4:A13;0);)"";COLUMN(A4:G4);"")))
Too long , but dynamic , old school .
The ? should be the Omega sign.
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)
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
=LOOKUP(2,1/(A3:A13=K3),D3:D13) works for me.
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.
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 😉
Interesting ?
One option I found is with TEXTJOIN and IF: =TEXTJOIN(,TRUE,IF(A4:A13=$K3,IF(ISBLANK(C4:F13),B4:E13&"",""),""))
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))) )
oops....inside IFS there should be "not equal" sign in the logical parts.
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.
=LET(rng,VLOOKUP(K3,A3:E13,{2,3,4,5},FALSE),XLOOKUP(FALSE,ISBLANK(rng),(rng),,,-1))
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))
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))
Awesome formulas David 🙂
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))
=LOOKUP("zzz",OFFSET(A1,MATCH(K3,A:A,0)-1,,,1000))
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
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)
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
{=INDEX(OFFSET($A$3,MATCH(K3,A4:A13,0),1,,4), MAX((OFFSET($A$3,MATCH(K3,A4:A13,0),1,,4)0)*{1,2,3,4}))}