Index And Match Problem

[manishmaheshwari]

Hello

Please help me to sort this query.

I have have 3 Sheet

Sheet1 data is (Only data entry)

[TABLE="class: grid, width: 681"]
<colgroup><col><col><col><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD]DATE
[/TD]
[TD]B.NO[/TD]
[TD]PRODUCT
[/TD]
[TD]BL NO
[/TD]
[TD]SILICA[/TD]
[TD]HM-100[/TD]
[TD]SILICA ADD TO DISCHARGE TIME
[/TD]
[TD]TOTAL OUT PUT[/TD]
[TD]LOSS[/TD]
[/TR]
[TR]
[TD]02.01.19[/TD]
[TD]1/01[/TD]
[TD]HM-65
[/TD]
[TD]1-A[/TD]
[TD]400[/TD]
[TD]701[/TD]
[TD]60 MINUTE[/TD]
[TD]1100[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2
[TABLE="class: grid, width: 500"]
<colgroup><col style="mso-width-source:userset;mso-width-alt:3145;width:65pt" width="86"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:4900;width:101pt" width="134"> <col style="width:48pt" width="64" span="3"> <col style="mso-width-source:userset;mso-width-alt:3584;width:74pt" width="98"> <col style="mso-width-source:userset;mso-width-alt:3657;width:75pt" width="100"> <col style="mso-width-source:userset;mso-width-alt:3328;width:68pt" width="91"> </colgroup><tbody>[TR]
[TD="width: 86"]DATE
[/TD]
[TD="width: 77"]B.NO[/TD]
[TD="width: 134"]PRODUCT[/TD]
[TD="width: 64"]ASH[/TD]
[TD="width: 64"]LOD[/TD]
[TD="width: 64"]SIEVE[/TD]
[TD="width: 98"]MOISTURE[/TD]
[TD="width: 100"]CUSTOMER[/TD]
[TD="width: 91"]REMARKS[/TD]
[/TR]
[TR]
[TD]02.01.19[/TD]
[TD]1/01[/TD]
[TD]HM-65[/TD]
[TD]33.17[/TD]
[TD]3.87[/TD]
[TD] 0.00
[/TD]
[TD]0.00
[/TD]
[TD]JK
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
having formula from sheet1(date b-no product column)
Sheet3 (having column of DATA SHEET1+SHEET)

SHEET3 COLUMN AS BELOW
[TABLE="class: grid, width: 1177"]
<colgroup><col width="79"><col width="67"><col width="77"><col width="64" span="3"><col width="126"><col width="76"><col width="64" span="4"><col width="98"><col width="115"><col width="91"></colgroup><tbody>[TR]
[TD="width: 79"]DATE
[/TD]
[TD="width: 67"]B.NO[/TD]
[TD="width: 77"]PRODUCT[/TD]
[TD="width: 64"]BL NO[/TD]
[TD="width: 64"]SILICA[/TD]
[TD="width: 64"]HM-100[/TD]
[TD="width: 126"]SILICA ADD TO DISCHARGE TIME[/TD]
[TD="width: 76"]TOTAL OUT PUT[/TD]
[TD="width: 64"]LOSS[/TD]
[TD="width: 64"]ASH[/TD]
[TD="width: 64"]LOD[/TD]
[TD="width: 64"]SIEVE[/TD]
[TD="width: 98"]MOISTURE[/TD]
[TD="width: 115"]CUSTOMER[/TD]
[TD="width: 91"]REMARKS[/TD]
[/TR]
</tbody>[/TABLE]

FORMULA IS =IFERROR(INDEX(REPORT!A$2:$O$345,SMALL(IF($N$3=REPORT!$N$3:$N$345,ROW(REPORT!$N$3:$N$345)-3,""),1),1),"")

BUT AFTER SOME ROWS IT GIVE BLANK ROW AND AGAIN DATA CAN BEEN SEEN

 

[RasGhul]

Is the Product code HM-65 unique on both sheets?

 

[AhoyNC]

Maybe:
Array formula enter with CTRL-SHIFT-ENTER

=IFERROR(INDEX(REPORT!A$2:$O$345,SMALL(IF($N$3=REPORT!$N$3:$N$345,ROW(REPORT!$N$3:$N$345)-ROW($N$3)+1),COUNTIF($N$3:N3,N3)),1),"")

 

[manishmaheshwari]

Maybe:
Array formula enter with CTRL-SHIFT-ENTER

=IFERROR(INDEX(REPORT!A$2:$O$345,SMALL(IF($N$3=REPORT!$N$3:$N$345,ROW(REPORT!$N$3:$N$345)-ROW($N$3)+1),COUNTIF($N$3:N3,N3)),1),"")

thnx AhoyNC