Removing #N/A

[mrsushi]

Good afternoon,

I have the below formula which is doing what its supposed to. However, its flagging up #N/A as the underlying raw data isnt found.

=IF(INDEX('Paste LE22'!E:E,MATCH(B2,IF('Paste LE22'!C:C=ALL!E2,'Paste LE22'!A:A),0))=0,"Zero Holding", "None zero")

To get rid of these, i have tried using IFERROR, but the formula doesn't appear to like it.

Any ideas please?

Regards
M

 

[TomeK712]

=IFNA(IF(INDEX('Paste LE22'!E:E,MATCH(B2,IF('Paste LE22'!C:C=ALL!E2,'Paste LE22'!A:A),0))=0,"Zero Holding", "None zero"),"")

 

[Joe4]

How exactly have you been trying to implement IFERROR?
You should try it like this:

=IFERROR(IF(INDEX('Paste LE22'!E:E,MATCH(B2,IF('Paste LE22'!C:C=ALL!E2,'Paste LE22'!A:A),0))=0,"Zero Holding", "None zero"),"")

 

[Joe4]

=IFNA(IF(INDEX('Paste LE22'!E:E,MATCH(B2,IF('Paste LE22'!C:C=ALL!E2,'Paste LE22'!A:A),0))=0,"Zero Holding", "None zero"),"")

The only problem with that is if their profile is accurate, and they are really using Excel 2010, the IFNA function was not added until after that, so it would not be available to them (IFNA function - Microsoft Support.).

 

[mrsushi]

How exactly have you been trying to implement IFERROR?
You should try it like this:

=IFERROR(IF(INDEX('Paste LE22'!E:E,MATCH(B2,IF('Paste LE22'!C:C=ALL!E2,'Paste LE22'!A:A),0))=0,"Zero Holding", "None zero"),"")

That's perfect. I had missed out the (IF part

Many thanks for your help.
M

 

[Joe4]

You are welcome.
Glad we were able to help!