Handling #N/A in a cell

[PreacherUK]

Hi all,

I have the following code in a series of cells

=INDEX(NewPrices!B:B,MATCH($A4,NewPrices!$A:$A,0))

This is looking up a value in a sheet based on an identifier. If the identifier is not in the 'look up' list it is returning a value of #N/A (quite correctly). Is there an accepted/preferred way of handling this error message? something along the lines of:

if(INDEX(NewPrices!B:B,MATCH($A4,NewPrices!$A:$A,0))=#N/A) then make cell =0

I'm sure there is, I'm just not familiar enough with the odd syntax

Any help would be greatly appreciated and richly rewarded in gold stars

 

[PreacherUK]

Just an update:


the below seems to work


if(ISNA('Formula'),'OutPut Value')

Who'd have believed there was a function for the #N/A value?

 

[SQLBI]

Hi,

For your information, there's also

ISERR()

and

ISERROR()

which you may find useful in the future.

ISNA()

will only capture #N/A values and

ISERR()

will capture everything BUT #N/A.

ISERROR

is a 'catch all'.

Hope this helps.



Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[TheBitDoctor]

Your on the right track. Just use the formula twice:

=IF(ISERROR(MATCH(5,A1:A20,0)),0,MATCH(5,A1:A20,0))

Once to check for the error and the second to insert the corect value incase there isn't any error. Just be sure to replace the error value with one that can't be a part of your solution set. In this case the match function returns 1 through the size of the array so 0 would be a valid error indicator. -1 would be another.