Need help with this function

vir0020

New Member
Joined
Mar 19, 2019
Messages
2
=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.
 

Some videos you may like

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
Joined
Oct 20, 2009
Messages
8,111
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 19, 2019
Messages
2
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
Joined
Oct 20, 2009
Messages
8,111
Office Version
  1. 365
Platform
  1. Windows
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))))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,466
Messages
5,528,972
Members
409,848
Latest member
Blomsten
Top