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.
3 Responses to “How-to create an elegant, fun & useful Excel Tracker – Step by Step Tutorial”
Hi Chandoo,
I am responsible for tracking when church reports are submitted on time or not and the variations from the due date for submission.
Here is the Scenario;
The due date for the submission of monthly reports is on the 5th of each month. and I would like to know how many reports have been submitted on time (i.e, those that have been submitted on or before the due date) I would also want to track those reports that have been submitted after the due date has passed.
How can I create such a tracker?
Hi Chandoo,
I am a member of your excel school.
I was trying to create SOP Tracker I follow all your steps but I keep this error below.
The list source must be a delimited list, or a reference to a single row or cell.
I try looking on YouTube for answer but no luck.
can you help on this?
thanks
Carl.
Dear Mr. Chando,
Rakesh, I'm working in a private company in the UAE. Recently, I'm struggling to get more details about the staff sick, annual, unpaid, and leaves. I would like to get a tracker in excel. Could you please help me in this situation?
I also watching your videos in YouTube. i hope you can help me on this situation.