Why does the index and aggregate function shows an error #NUM when there is valid data

[liabilityquek]

Hi All

I have a list of invoices number stating the various dates(rejected, processing, approved and paid) and the status for based on the dates in sheet 1. There would be duplicate invoice numbers showing the latest date and the latest status. Therefore, I have used a pivot (sheet4) to eliminate duplicate invoice numbers and also used an index and aggregate function to identify the latest date and the status based on the latest date.

Attached is the data file that i am working on it.

Much appreciated!

 

[bosco_yip]

Hi,

Please change your formula in I5,

From this >>

=INDEX(Sheet1!F:F,AGGREGATE(15,6,ROW($A$5:$A$3004)/($A5=Sheet1!$A$2:$A$3177),COUNTIF($A$5:$A5,$A5)))

Into this >>

=INDEX(Sheet1!F:F,AGGREGATE(15,6,ROW($A$5:$A$3177)/($A5=Sheet1!$A$2:$A$3177),COUNTIF($A$5:$A5,$A5)))

then,

formula copied right to J5 and all copied down

Regards
Bosco

 

[liabilityquek]

Hi,

Please change your formula in I5,

From this >>

=INDEX(Sheet1!F:F,AGGREGATE(15,6,ROW($A$5:$A$3004)/($A5=Sheet1!$A$2:$A$3177),COUNTIF($A$5:$A5,$A5)))

Into this >>

=INDEX(Sheet1!F:F,AGGREGATE(15,6,ROW($A$5:$A$3177)/($A5=Sheet1!$A$2:$A$3177),COUNTIF($A$5:$A5,$A5)))

then,

formula copied right to J5 and all copied down

Regards
Bosco

Thanks for the formula previously! Didn't check the error too.

 

[liabilityquek]

Hi,

Please change your formula in I5,

From this >>

=INDEX(Sheet1!F:F,AGGREGATE(15,6,ROW($A$5:$A$3004)/($A5=Sheet1!$A$2:$A$3177),COUNTIF($A$5:$A5,$A5)))

Into this >>

=INDEX(Sheet1!F:F,AGGREGATE(15,6,ROW($A$5:$A$3177)/($A5=Sheet1!$A$2:$A$3177),COUNTIF($A$5:$A5,$A5)))

then,

formula copied right to J5 and all copied down

Regards
Bosco

Hi Bosco, after i have amended on the rows, it seems that there are some errors; like for instance invoice 4119004267 in Sheet1 is showing a rejected status, but is showing other wise in Sheet4. Another instance would be invoice 4119002531 in Sheet1 is showing the latest rejected date of 29/11/2019 but it is showing 0/1/1900 in Sheet4.

Is there another formula which i could use?

 

[bosco_yip]

Another mistake found !

Change the formula

From this >>

=INDEX(Sheet1!F:F,AGGREGATE(15,6,ROW($A$5:$A$3004)/($A5=Sheet1!$A$2:$A$3177),COUNTIF($A$5:$A5,$A5)))

Into this >>

=INDEX(Sheet1!F:F,AGGREGATE(15,6,ROW($A$2:$A$3177)/($A5=Sheet1!$A$2:$A$3177),COUNTIF($A$5:$A5,$A5)))

Regards
Bosco

 

[liabilityquek]

Another mistake found !

Change the formula

From this >>

=INDEX(Sheet1!F:F,AGGREGATE(15,6,ROW($A$5:$A$3004)/($A5=Sheet1!$A$2:$A$3177),COUNTIF($A$5:$A5,$A5)))

Into this >>

=INDEX(Sheet1!F:F,AGGREGATE(15,6,ROW($A$2:$A$3177)/($A5=Sheet1!$A$2:$A$3177),COUNTIF($A$5:$A5,$A5)))

Regards
Bosco

Hi Bosco, my apologies. I did not attached the revised file previously. i have amended and attached the revised file.

 

[bosco_yip]

Hi Bosco, my apologies. I did not attached the revised file previously. i have amended and attached the revised file.

As my post #5 mentioned, " Change from : Row($A$5 to Row($A$2 "

Regards