Find the first & last invoice no

[Bibhuti Mohanty]

Dear Excel Expert.

Please suggest me how to find the First & Last Invoice no on a given column.
For your reference please find the attached file.

Thanks & regards,

 

[Deepak]

First

=LOOKUP(2,1/(LEFT($C$2:$C$47,4)="5530")*ROW($C$2:$C$47),$C$2:$C$47)

Last

=LOOKUP(2,-1/(LEFT($C$2:$C$47,4)="5530")*ROW($C$2:$C$47),$C$2:$C$47)

 

[Bibhuti Mohanty]

Dear Expert,

Thanks for your helpful & prompt reply. In addition i need the first date & last date on the reference of invoice no. would you please suggest.

 

[Deepak]

Check it

 

[Bibhuti Mohanty]

Thank your sir.
but whenever i am using this formula to find the invoice no from another sheet it does not working. Please suggest.

 

[Deepak]

Thank your sir.
but whenever i am using this formula to find the invoice no from another sheet it does not working. Please suggest.

Would you pls share that what have you done in real data so that i can help you in more elegant way!

 

[Bibhuti Mohanty]

Sir,Please check the attached file, what we need.

 

[Deepak]

File??

 

[bosco_yip]

First
=LOOKUP(2,1/(LEFT($C$2:$C$47,4)="5530")*ROW($C$2:$C$47),$C$2:$C$47)
......

=LOOKUP(2,1/(LEFT($C$2:$C$47,4)="5531")*ROW($C$2:$C$47),$C$2:$C$47)

The First Invoice formula will give #N/A, if the criteria changed from 5530 to 5531

Regards

 

[bosco_yip]

For Invoice no range from 5530,

1] The First Invoice no / Invoice date, formula copy across :

=INDEX(D$2:D$47,MATCH(1,INDEX(FIND( 5530,$C$2:$C$47),0),0))

or,

=INDEX(C$2:C$47,INDEX(MATCH( 5530&"*",$C$2:$C$47&"",0),0))

2] The Last Invoice no / Invoice date, formula copy across :

=LOOKUP(1,-FIND( 5530,$C$2:$C$47),C$2:C$47)

Regards
Bosco

 

[Deepak]

Oh! By bad one was that!

Check this!

=INDEX($C$2:$C$47,SMALL(IF(LEFT($C$2:$C$47,4)="5530",ROW($C$2:$C$47)-1),1))

=INDEX($C$2:$C$47,LARGE(IF(LEFT($C$2:$C$47,4)="5530",ROW($C$2:$C$47)-1),1))

Both are array formula so use, Ctrl+Shift+Enter not just Enter...

 

[BrajaKishore]

can any body help me out to get latest date, where value is greater than zero. file attached

 

[vletm]

BrajaKishore
As You a new member and
You have read Forum Rules ... today,
then You should reread those rules again and
please, follow too.
= You should open a new thread as written.

 

[Ateeb Ali]

check attached file if this helps
press like button if problem solved