• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do i lookup the latest status of an invoice based on the latest date identified?

Hi All,

I have a set of invoice numbers which has rejected dates, processing dates, approving dates and paid dates. I have used MAX function to identify the latest date for each particular invoice. However, i would like to identify each status of each invoices; ie rejected, processing, approved or paid based on the latest dates. These are all classified as Table 1.

And also, i would like to vlookup a list of invoice numbers on the latest dates and status from Table 1. However, there are duplicate invoices and would like to find out what is the latest date and status.

Attached is the excel file for your reference.

Thank you for the assistance.
 

Attachments

  • Query.xlsx
    10.8 KB · Views: 5
1] "Table 1 - Status" G3, copied down :
=SUBSTITUTE(IF(B3=F3,B$2,IF(C3=F3,C$2,IF(D3=F3,D$2,IF(E3=F3,E$2))))," date","")

2] "Table 2 - Status" J3, copied down :
=INDEX(G:G,AGGREGATE(15,6,ROW($I$3:$I$17)/($I3=$A$3:$A$17),COUNTIF($I$3:$I3,$I3)))

3] "Table 2 - Date" K3, copied down :
=INDEX(F:F,AGGREGATE(15,6,ROW($I$3:$I$17)/($I3=$A$3:$A$17),COUNTIF($I$3:$I3,$I3)))

66269

Regards
 
Back
Top