Recently, I was working on setting the agenda for a meeting. I had a list in Excel where I had a list of people and the dates on which they acted as the ‘Meeting Chair’. Since there was repetition in the list (which means that a person has been Meeting Chair multiple times), I also needed to know when was the last time a person acted as the ‘Meeting Chair’. This was because I had to ensure someone who recently chaired was not assigned again. So I decided to use some Excel Function magic to get this done. Below is the final result where I am able to select a name from the drop-down and it gives me the date of the last occurrence of that name in the list.
If you have a good understanding of Excel Functions, you would know that there is no one Excel function that can do this. But you are in the Formula Hack section, and here we make the magic happens. In this tutorial, I’ll show you three ways to do this.
Find the Last Occurrence – Using MAX function
Credit to this technique goes to an article by Excel MVP Charley Kyd. Here is the Excel formula that will return the last value from the list: Here is how this formula works:
The MAX function is used to find the row number of the last matching name. For example, if the name is Glen, it would return 11, as it’s in the 11 row. Since our list starts from second row onwards, 1 has been subtracted. So the position of the last occurrence of Glen is 10 on our list. SUMPRODUCT is used to ensure that you don’t have to use Control + Shift + Enter, as SUMPRODUCT can handle array formulas. INDEX function is now used to find the date for the last matching name.
Find the Last Occurrence – Using LOOKUP function
Here is another formula to do the same job:
Here is how this formula works:
The lookup value is 2 (you’ll see why.. keep reading) The lookup range is 1/($A$2:$A$14=$D$3) – This returns 1 when it finds the matching name and an error when it doesn’t. So you end up getting an array. For example, of the lookup value is Glen, the array would be {#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!}. The third argument ([result_vector]) is the range from which it gives the result, which are dates in this case.
The reason this formula works is that the LOOKUP function uses the approximate match technique. This means that if it can find the exact matching value, it would return that, but if it can not, it will scan the entire array till the end and return the next largest value which is lower than the lookup value. In this case, the lookup value is 2, and in our array, we will only get 1’s or errors. So it scans the entire array and returns the position of the last 1 – which is the last matching value of the name.
Find the Last Occurrence – Using Custom Function (VBA)
Let me also show you another way of doing this. We can create a custom function (also called User Defined Function) using VBA. The benefit of creating a custom function is that it’s easy to use. You don’t have to worry about creating a complex formula every time, as most of the work happens in the VBA backend. I have created a simple formula (which is a lot like VLOOKUP formula). To create a custom function, you need to have the VBA code in the VB Editor. I will give you the code and the steps to place it in the VB Editor in a while, but let me first show you how it works: This is the formula that will give you the result:
The formula takes three arguments:
Lookup Value (this would be the name in cell D3) Lookup Range (this would be the range that has the names and dates – A2:B14) Column Number (this is the column from which we want the result)
Once you have created the formula and put the code in VB Editor, you can use it just like any other regular Excel worksheet functions. Here is the code for the formula: Here are the steps to place this code in the VB Editor: Now the formula would be available in all the worksheet of the workbook. Note that you need to save the workbook in the .XLSM format as it has a macro in it. Also, if you want this formula to be available in all the workbooks you use, you can either save it to the Personal Macro Workbook or create an add-in from it.
How to get the last number in a list in Excel. Using FIND Function in Excel. How to Return Cell Address Instead of Value in Excel Find Position of the Last Occurrence of a Character in a String in Excel
I want the table to look like this: Prod Order ID Apple Cust:1 Cake Cust:1 Coke Cust:1 Tree Cust:2 Apple Cust:2 Plant Cust:2 Any help would be appreciated. Excel or Access Unit No Name Amount Owing Unit 1 Royce Smith -$20.00 Unit 2 Elly Howard $0.00 Unit 3 Sean Bright $0.00 Unit 4 Vicki Brown $0.00 =LastItemLookup($B2,’Unit 1′!$A$2:$J$1048576,10) Thanks For example column A has the home team (the team I want to find the most recent game played before today) Column B has the away team Column C has the score I would like to be able to find the team in column A’s most recent game played before today and extract the their opponent (column b) and the score (column c). Any help would be greatly appreciated. Thanks. I have a set of stock units transacted (ordered by date from old to new) and I want to find the Previous Quantity that was transacted. Here is a sample dataset: Date Type Stock Qty PrevQty CumulativeQty 2016-01-03 Buy MSFT 100 0 100 2016-01-04 Buy GOOG 500 0 500 2016-01-05 Buy MSFT 100 100 200 2016-01-06 Sell MSFT 100 100 100 I am able to figure out CumulativeQty via SUMIFS but unable to do PrevQty. Any help would be highly appreciated. Best, -Ash Where First A:A is the range where you are looking for last occurrence (Can be any other range E.G. A4:B12). Row(A:A) – relative multiplier (Can be any letter, but should be same first and last rows as in first range. E.G. if first range is A4:B12, row() should be row(A4:A12) or Row(A4:B12) or Row(C4:C12). last A:A is the range “Apple” can be anything you are looking for e.g. Number, Text, or cell reference (like D4) P.S. If you are looking for 0, formula above will return last occurrence of 0 or blank cell. If you are looking exactly for 0 and not blank cell, use: =MAX(IF(A:A=””,0,IF(A:A=0,1,0))*ROW(A:A),0) I looking to find the last “ok” in the row then give me the number the row next to it. tried =LOOKUP(“ok”,G:G,F:F) gives not the last one but in the middle. tried =OFFSET(INDEX(F:F,MATCH(“ok”,G:G)),0,0) gives the same result. see attached picture I tried to get the balance next to the last “ok” balance in row F “ok” in row G thanks in advance, IMC I tried this formula and it seems that your formula is returning the last date in some of the items and in other cases the date before the last occurrence. Am I doing something wrong in my setup? When I change the first argument in the Match function from 1 to 2, everything seems to work fine. When the argument is 1, it seems to work for some but not for others. {=+INDEX($J$2:$J$15,MATCH(1,MATCH($I$2:$I$15,L3,0),1))} Feb 14 is the last occurrence for Steve and Jan 14th is the first occurrence. Is there something wrong in my set up Steve 16-Jan-14 (Formula 2) 13-Feb-14 (Formula 1 – correct)
