• 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.

Find the first & last invoice no

Bibhuti Mohanty

New Member
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,
 

Attachments

Bibhuti Mohanty

New Member
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.
 

bosco_yip

Excel Ninja
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
 
Last edited:

bosco_yip

Excel Ninja
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
 
Last edited:

Deepak

Excel Ninja
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...
 

vletm

Excel Ninja
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.
 
Top