Auto increment the Invoice number

[A_SIRAT]

Good day,

Please assist me with a formulae to auto increment the Invoice number after looking up the previous value for the specific client.

See attached file.

Thanks.

[#2]

@A_SIRAT 

How about this?

In B25

=IF(AND(ISNUMBER(B24),D25=D24),B24,INDEX($B$3:$B$20,MATCH(D25,$D$3:$D$20,0))+1)

and copy it down.

[#3]

@A_SIRAT 

Or simply this...

In B25

=IF(D25=D24,B24,INDEX($B$3:$B$20,MATCH(D25,$D$3:$D$20,0))+1)

[#4]

Thank you Subodh.

However, I expected the Invoice nr. to auto-increment by 1 as long it is a new shipment to a specific customer. May be I did not explain myself properly, but the next shipment for let's say Al Hilal, should have Invoice nr. 33, then 34, then 35. The Delivery note number also increments per delivery and is not bound to a customer. Same with the other customers.

[#5]

@A_SIRAT 

Okay try this and see if it returns the desired output.

In B25

=IF(AND(C25=C24,D25=D24),B24,IF(ISNUMBER(MATCH(D25,INDEX(D$24:D24,),0)),MAX(IFERROR(INDEX((D$24:D24=D25)*(B$24:B24),),0))+1,INDEX($B$3:$B$20,MATCH(D25,$D$3:$D$20,0))+1))

and copy it down.

[#6]

@A_SIRAT , as a variation to @Subodh_Tiwari_sktneer 's solution (if you have the latest Dynamic Array version of Excel):

=MAX(FILTER($B$3:$B24,($D$3:$D24=D25)*($C$3:$C24<C25))) + 1