# Get Rid of N/A

B

#### Bundex

Greetings
When I use This Formular i get the n/a answer when data is no
available.
Can i use the (ISNA) Formular or something Similar so when the data i
not available it does not show n/a.

If I can, where would it go.

=INDEX(JULY!\$A\$6:\$F\$800,MATCH(\$A3,JULY!\$A\$6:\$A\$800,0),3)

Thanks

Bunde

P

#### papou

Hi Bundex
=IF(ISNA(INDEX(JULY!\$A\$6:\$F\$800,MATCH(\$A3,JULY!\$A\$6:\$A\$800,0),3)),"",=INDEX(
JULY!\$A\$6:\$F\$800,MATCH(\$A3,JULY!\$A\$6:\$A\$800,0),3))

Regards
Pascal

A

#### Andy B

Hi

Pascal has left in the = sign in the middle. Take it out!! <g>

--
Andy.

papou said:
Hi Bundex
=IF(ISNA(INDEX(JULY!\$A\$6:\$F\$800,MATCH(\$A3,JULY!\$A\$6:\$A\$800,0),3)),"",=INDEX(
JULY!\$A\$6:\$F\$800,MATCH(\$A3,JULY!\$A\$6:\$A\$800,0),3))

Regards
Pascal

"Bundex >" <<Bundex.17xw2j@excelforum-nospam.com> a écrit dans le message de

P

#### papou

Thanks Andy ;-)

Andy B said:
Hi

Pascal has left in the = sign in the middle. Take it out!! <g>

--
Andy.

papou said:
Hi Bundex
=IF(ISNA(INDEX(JULY!\$A\$6:\$F\$800,MATCH(\$A3,JULY!\$A\$6:\$A\$800,0),3)),"",=INDEX(
JULY!\$A\$6:\$F\$800,MATCH(\$A3,JULY!\$A\$6:\$A\$800,0),3))

Regards
Pascal

"Bundex >" <<Bundex.17xw2j@excelforum-nospam.com> a écrit dans le
message

R

#### Ron Rosenfeld

Greetings
When I use This Formular i get the n/a answer when data is not
available.
Can i use the (ISNA) Formular or something Similar so when the data is
not available it does not show n/a.

If I can, where would it go.

=INDEX(JULY!\$A\$6:\$F\$800,MATCH(\$A3,JULY!\$A\$6:\$A\$800,0),3)

Thanks

Bundex
In addition to eliminating it with the ISNA function, you can also make it
invisible using Conditional Formatting. (Formula Is: =ISNA(A1))

This might have an advantage if you were graphing the results.

--ron

C

I generally use an IF statement and ISERROR so :

IF(ISERROR(INDEX)=true,0,INDEX)

Since this gets to be a pain keeping up with all the (( and )) start
by typing your formula then put an

ISERROR( at the front and a ) at the end.

This should return TRUE.

Then just put an IF( in front of all that and at the end
=TRUE,0,<paste formula> and a ) and that should do it. Basically
you are saying IF the formula returns an error insert a zero if it
will work run it. I do not like posting a formula without being able
to replicate it but it should look like this :

=IF(ISERROR(INDEX(JULY!\$A\$6:\$F\$800,MATCH(\$A3,JULY!\$A\$6:\$A\$800,0),3))=true,0,INDEX(JULY!\$A\$6:\$F\$800,MATCH(\$A3,JULY!\$A\$6:\$A\$800,0),3))

Hope this helps.

H

#### Harlan Grove

When I use This Formular i get the n/a answer when data is not
available.
Can i use the (ISNA) Formular or something Similar so when the data is
not available it does not show n/a.

If I can, where would it go.

=INDEX(JULY!\$A\$6:\$F\$800,MATCH(\$A3,JULY!\$A\$6:\$A\$800,0),3)
Another variation. First a question. If the col C value itself were #N/A for the
col A value matching A3, would you also want that #N/A value masked? If so, use
the other alternatives already proposed. If you're just trying to catch
situation in which A3 matches none of the col A values, then consider

=IF(COUNTIF(JULY!\$A\$6:\$A\$800,\$A3),VLOOKUP(\$A3,JULY!\$A\$6:\$F\$800,3,0),"")