Need help with this function

[vir0020]

=IF(ISBLANK(INDEX('Awin Update'!C:C,MATCH(Sheet1!A468824,'Awin Update'!$AW:$AW,0))),IF(ISBLANK(INDEX('net Bricorama'!A:A,MATCH(Sheet1!A468824,'net Bricorama'!$P:$P,0))),IF(ISBLANK(INDEX(ean!A:A,MATCH(Sheet1!A468824,ean!$I:$I,0))),IF(ISBLANK(INDEX(bour!A:A,MATCH(Sheet1!A468824,bour!$I:$I,0))),"notfound",INDEX(bour!A:A,MATCH(Sheet1!A468824,bour!$I:$I,0))),INDEX(ean!A:A,MATCH(Sheet1!A468824,ean!$I:$I,0))),INDEX('net Bricorama'!A:A,MATCH(Sheet1!A468824,'net Bricorama'!$P:$P,0))),INDEX('Awin Update'!C:C,MATCH(Sheet1!A468824,'Awin Update'!$AW:$AW,0)))

I'm trying to parse different csv into one csv file where the main key is the EAN number which is in the array AW:AW, Product EAN is at Sheet1!A2:A600000. The 1st file works perfect, when it comes to the the other files it comes up as #N/A tried the ISBLANK doesn't work, IF ="" doesn't work IF <0 doesn't work too. any suggestions.

 

[steve the fish]

Hi. The problem you have there is if the match lookup value is not found in the appropriate column then the match will return na. Isblank will then produce false so your formula will try to return the index number of an na result hence the formula returns na. To give an example take the first test:

ISBLANK(INDEX('Awin Update'!C:C,MATCH(Sheet1!A468824,'Awin Update'!$AW:$AW,0)))

Should Sheet1!A468824 not be found in 'Awin Update'!$AW:$AW then the match produces na which in turn means the index produces na and the isblank produces false. Your value if false for that test is:

INDEX('Awin Update'!C:C,MATCH(Sheet1!A468824,'Awin Update'!$AW:$AW,0))

which produces na as previously stated.

 

[vir0020]

Yes it was FALSE and N/A, but my main thing is i need it to index from other csvfiles(sheets) to produce the info in the active sheet.

 

[steve the fish]

You could add an OR to the IF test eg for the first IF:

=IF(OR(ISBLANK(INDEX('Awin Update'!C:C,MATCH(Sheet1!A468824,'Awin Update'!$AW:$AW,0))),ISERROR(MATCH(Sheet1!A468824,'Awin Update'!$AW:$AW,0))),TRUE,FALSE)

or if you can be certain the last cell on the worksheet is blank you could do:

=ISBLANK(INDEX('Awin Update'!C:C,IFERROR(MATCH('Awin Update'!A4,'Awin Update'!$AW:$AW,0),ROWS(C:C))))