Search code examples
excelexcel-formulaworksheet-function

Find The Last Value of Range With Criteria and Return Value of Another Cell

I need to figure out the formula to get last messageid based on replyto within a threadid.

Below is an example. Basically the last instance of a messageid within a threadid I need to return the value of messageid in "Last Thread Post ID". My spreadsheet will always be sorted as shown.

The first post in a thread has a replyto number of 0.

I have a database with tens of thousands of messageid and threadid, so I can't do it manually like I did below. I'm not sure how to set up a formula or macro to perform this task. Any help is appreciated. 

messageid   threadid    replyto    Last messageid in thread
3550        3550        0          3550
3867        3867        0          4677
3947        3867        3867        
3998        3867        3947        
4337        3867        3998        
4414        3867        4337        
4481        3867        4414        
4677        3867        3867        
3925        3925        0          7200
4653        3925        3925        
4959        3925        4653        
7200        3925        3925        
4092        4092        0          4092
5800        5800        0          5800
5802        5802        0          5802
7202        7202        0          7202
7203        7203        0          7656
7294        7203        7203        
7305        7203        7203        
7342        7203        7203        
7406        7203        7342        
7436        7203        7305        
7656        7203        7203
Solution

  • Use this in D2 and copy down:

    =IF(C2=0,AGGREGATE(14,6,$A$2:$A$24/($B$2:$B$24=B2),1),"")

    enter image description here