# To arrange Serial wise as per rate given by agency

#### paramveer

##### Member
Sir I want lowest tender rate given by firm come to First No and arrangement of Rest all the firms not a issue. Example given in excel sheet. Thanks for help to solve my problem.

#### Attachments

• 12.1 KB Views: 5

#### bosco_yip

##### Excel Ninja
Try,

In B20, copied across and down :

=INDEX(\$B\$5:\$Q\$7,MATCH(\$A20,\$A\$5:\$A\$7,0),IF((A\$16="Govt Rate")+(B\$16="Govt Rate"),(B\$18="Amount")+1,MATCH(IF(B\$17>0,B\$17,A\$17),\$B\$2:\$P\$2,0)+(B\$18="Amount")))

In C23, copied across :

=IF(C18="Amount",SUM(C20:C22),"")

Regards
Bosco

#### Attachments

• 13.6 KB Views: 5

#### paramveer

##### Member
Try,

In B20, copied across and down :

=INDEX(\$B\$5:\$Q\$7,MATCH(\$A20,\$A\$5:\$A\$7,0),IF((A\$16="Govt Rate")+(B\$16="Govt Rate"),(B\$18="Amount")+1,MATCH(IF(B\$17>0,B\$17,A\$17),\$B\$2:\$P\$2,0)+(B\$18="Amount")))

In C23, copied across :

=IF(C18="Amount",SUM(C20:C22),"")

Regards
Bosco

#### bosco_yip

##### Excel Ninja
Please see attached revised file with detail explanation.

Regards
Bosco

#### Attachments

• 15.5 KB Views: 7

#### paramveer

##### Member
Thanks You So much Sir for solving my problem