I know what you want to ask: Why are you using Numbers when Excel is so much better and more versatile? Excellent question! I ask myself that all the time.
There are many things you can do with Excel that can’t be done in Numbers, and it often seems like I find a new one every day. Let’s face it, Numbers isn’t intended for the same users as Excel.
That being said, I don’t like Excel and I don’t even have a good reason for it. Maybe it’s a natural aversion to monopolies or something. Maybe it’s dedication to a brand – I’ve been a loyal Apple user since the days of the Apple ][. Well, maybe not that loyal. I don’t like iPhones or iPads. But you should ask me about iGlasses sometime.
Regardless. I ran into a problem while creating a spreadsheet in Numbers. I was about to relent and create it in Excel when I ran into a similar problem.
How to Return the Last Occurrence of a Value from a List of Values. Here’s my problem in detail:
The Practice Items column is a list of the songs I’m currently practicing on the piano, and I want to keep track of how much time I spent practicing that piece and when I practiced it. The column in question is Last Practiced. I want a polite warning if I haven’t practiced a piece in a while.
The problem was getting that column to return the Last Time I Practiced It. For instance, I practiced Adele’s Someone Like You on April 4 and April 5, and I want April 5 to show up in the last column.
After messing around with Numbers for longer than I really should have, or longer than the problem really merited, I tried to do the same thing in Excel. To no avail. I was starting to feel really stupid.
So I googled the problem and found that different authors had presented three different ways to do it in Excel. And none of them will work in Numbers.
I was about to give up and use an Excel spreadsheet when my stubborn streak kicked in. There HAS to be a way to do this! So I made another pot of coffee and dug into it.
I used the template from the Excel solutions and modified the elements of the equation that returned errors in Numbers. My stubborn patience was rewarded.
If you came here looking for this solution, here it is: Ta da!
This is from the Someone Like You cell: IF(ISERROR(VLOOKUP(G4,Practice Item,1,0)),””,INDEX(Practice Item:Date,MATCH(G4,Practice Item,1),2))
The important part is INDEX(Practice Item:Date, Match(G4,Practice Item,1),2).
The if-iserror statement is simply so that it won’t put the last date on the list for items that I haven’t practiced.
I hope somebody finds this useful.
Memoirs of an Earthling 