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

Thanks & regards,

#### Attachments

• 8.6 KB Views: 12

#### Deepak

##### Excel Ninja
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)

Dear Expert,

Check it

#### Attachments

• 9.1 KB Views: 15

#### Bibhuti Mohanty

##### New Member
but whenever i am using this formula to find the invoice no from another sheet it does not working. Please suggest.

#### Deepak

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

##### New Member
Sir,Please check the attached file, what we need.

File??

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

#### BrajaKishore

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

#### Attachments

• 25.6 KB Views: 5

#### vletm

##### Excel Ninja
BrajaKishore
As You a new member and
You have read Forum Rules ... today,
then You should reread those rules again and