How can i change the #N/A error for a 0 value
 

I have a list of people......
a1:a3 b1:b3
7 Ana
8 Marie
9 Susan

I have the following formula:
=index(a1:b3,match("susan",b1:b3,0),1)
The result is: 9 But if the list does not have Susan, it gave me #N/A
error value.... i need to add the result in a formula...... but with the
error value it is not possible. is there any way to get 0 instead of #N/A
when the formula does not mach the list?

How can i change the #N/A error for a 0 value
 

=if(isna(match("susan",b1:b3,0)),0,index(a1:a3,mat ch("susan",b1:b3,0)))

(I changed your =index() a bit.)

And if you're using xl2007, look at =iferror() in excel's help.

Teresita wrote:

I have a list of people......
a1:a3 b1:b3
7 Ana
8 Marie
9 Susan

I have the following formula:
=index(a1:b3,match("susan",b1:b3,0),1)
The result is: 9 But if the list does not have Susan, it gave me #N/A
error value.... i need to add the result in a formula...... but with the
error value it is not possible. is there any way to get 0 instead of #N/A
when the formula does not mach the list?

--

Dave Peterson

How can i change the #N/A error for a 0 value
 

Try the following IF variation:

=IF(ISNA(INDEX(A1:B13,MATCH("susan",B1:B3,0),1)),0 ,INDEX(A1:B13,MATCH("susan",B1:B3,0),1))

--
Kevin Backmann


"Teresita" wrote:

I have a list of people......
a1:a3 b1:b3
7 Ana
8 Marie
9 Susan

I have the following formula:
=index(a1:b3,match("susan",b1:b3,0),1)
The result is: 9 But if the list does not have Susan, it gave me #N/A
error value.... i need to add the result in a formula...... but with the
error value it is not possible. is there any way to get 0 instead of #N/A
when the formula does not mach the list?