# Need help with this function

#### vir0020

##### New Member
=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.

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### steve the fish

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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))))

Replies
3
Views
55
Replies
3
Views
142
Replies
7
Views
86
Replies
22
Views
195
Replies
0
Views
233