Ever since Excel introduced the LAMBDA function, I’ve been itching to use it in real life. I got my first chance today.
We track the skill index of our different teams (consulting, analytics, technology, etc.) like this:
| Team | Skill Index | Apr-23 | May-23 | Jun-23 | Jul-23 |
|---|---|---|---|---|---|
| Consulting | 0% | 0% | |||
| Analytics | 33% | 33% | |||
| Technology | 72% | 72% | |||
| etc. |
The “Skill Index” column should pick the LAST value. If Apr-23 is filled, use that. But if May-23 is also filled, use that.
I needed something like a =LASTVALUE(range) formula. But none exists.
A good alternative is this formula to get the last non-empty cell:
=LOOKUP(2,1/(range<>""),range)
So, I followed the instructions to create a function in the Name Manager (Ctrl+F3)
… and simply fill in =LASTVALUE(H6:S6) and the like in the “Skills Index” cell.
The LOOKUP formula is confusing. My aim is to confuse our team less. But I wonder if they’ll start Google-ing for this LASTVALUE formula no one ever heard of, and get more confused 🤔.