Index And Match Problem

[manishmaheshwari]

Hello

Please help me to sort this query.

I have have 3 Sheet

Sheet1 data is (Only data entry)

DATE	B.NO	PRODUCT	BL NO	SILICA	HM-100	SILICA ADD TO DISCHARGE TIME	TOTAL OUT PUT	LOSS
02.01.19	1/01	HM-65	1-A	400	701	60 MINUTE	1100	0

<colgroup><col><col><col><col span="3"><col><col><col></colgroup><tbody>
</tbody>

Sheet2

DATE	B.NO	PRODUCT	ASH	LOD	SIEVE	MOISTURE	CUSTOMER	REMARKS
02.01.19	1/01	HM-65	33.17	3.87	0.00	0.00	JK

<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>
</tbody>

having formula from sheet1(date b-no product column)
Sheet3 (having column of DATA SHEET1+SHEET)

SHEET3 COLUMN AS BELOW

DATE

B.NO

PRODUCT

BL NO

SILICA

HM-100

SILICA ADD TO DISCHARGE TIME

TOTAL OUT PUT

LOSS

ASH

LOD

SIEVE

MOISTURE

CUSTOMER

REMARKS

<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>
</tbody>

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