I have 2 worksheets in an excel workbook.

Data

Column A                 Column B (PO)        Column C (Supplier No)           Column D (Item No)           Column E(date)         Column F (week no)
123406121601 - 555        =LEFT(A1,12)        =LEFT(A1,3)                      =RIGHT(A1,3)                 06/12/2016             =WEEKNUM(A1,21)

I have about about 1000 rows of data like the above. In column A, each cell contains jumbled information consisting of a PO number and item number.

The PO number is the first 12 digits in the cell in column A, and the first 4 digits contain the supplier number.

The PO also is made up of a date, i.e. 123406121601 = a date of 061016 and once formated = 06/12/2016.

My formula's give the results below:

Column A                 Column B         Column C   Column D   Column E       Column F
ABC123409121601 - 555    123409121601     123        555        06/12/2016     49

On the second sheet, Home, i have the following:

Column A    Column b      Column C       Column D      Column E
123         555           06/12/2016                   {INDEX MATCH FORMULA}

I am wanting to lookup the po number on sheet 2 where the supplier number, item number and date match - using the formula below:

=IF(C1<>"",INDEX(Data!B:B,MATCH(1,(Home!A1=Data!C:C)*(Home!B1=Data!D:D)*(Home!C1=Data!E:E),0)),INDEX(Data!B:B,MATCH(1,(Home!A1=Data!C:C)*(Home!B1=Data!D:D)*(D1=Data!F:F),0)))

Within this formula I have surrounded 2 index match formula's inside an if statement. This means the user can check for a result/PO Number based on the supplier number and item number and a specific date or the week number that date falls within.

For some reason, this index match works fine if im using static values and keeping my rows of data relatively short. However, for some reason, either by using formulas on sheet 'data' or by having more rows of data - this causes me to get the following result