how do you get rid of #n/a

mcfly · May 25th, 2002, 12:22 AM

=INDEX(C6:$C$203,MATCH(1,E6:$E$203,0)) this is the formula. I have tried to conditional format but I can't seem to figure it out!

Brian · May 25th, 2002, 12:39 AM

Your formula works fine, so most likely you are not pointing at the ranges you though you were.

#N/A! means that is could not find the data. If that is correct, but you want to display something other than #N/A, the use:

=If(isna(INDEX(C6:$C$203,MATCH(1,E6:$E$203,0)), "Not Found",INDEX(C6:$C$203,MATCH(1,E6:$E$203,0)) )

If you thins #N/A is an error, then first test: =MATCH(1,E6:$E$203,0) This is looking for 1 in the range E6:$E$203, which is a little unusual, did you intend to place a cell reference instead of 1?

Regards,

Brian

mcfly · May 25th, 2002, 12:57 AM

No, I needed to locate the 1st one(1). I am using this function to place specific text into a specific place in order by the 1st number it comes across.
It might be several ones(1) but all I need is the 1st and then I need the 1st two(2).
example:
1<-------need this because c=1
1<-------need this because oh=1
2<------need this because c=2
2<------need this because oh=2
and if there is no data in that cell it displays #N/A which I quess I could live with, but-- I don't want to.
Thanks!!!!!!!!!!

giacomo · May 25th, 2002, 01:07 AM

Quote:

On 2002-05-24 18:57, mcfly wrote:
No, I needed to locate the 1st one(1). I am using this function to place specific text into a specific place in order by the 1st number it comes across.
It might be several ones(1) but all I need is the 1st and then I need the 1st two(2).
example:
1<-------need this because c=1
1<-------need this because oh=1
2<------need this because c=2
2<------need this because oh=2
and if there is no data in that cell it displays #N/A which I quess I could live with, but-- I don't want to.
Thanks!!!!!!!!!!

Hi mcfly, I helped you with this formula yesterday. Brian has the right idea, in order to hide the #N/A you need to use the IF and ISNA formulas

=If(isna(MATCH(1,E6:$E$203,0)), "No MATCH",INDEX(C6:$C$203,MATCH(1,E6:$E$203,0)))

mcfly · May 25th, 2002, 01:10 AM

Thank For all the help maybe one day I will finish this project. I Couldn't do it without everyones help.

Brian from Maui · May 25th, 2002, 01:31 AM

mcfly,

Although I agree with Brian and giacommo, you can hide the #n/a by using conditional formatting. Use

=iserror(cell), set font color to match cell color.

Aladin Akyurek · May 25th, 2002, 07:37 AM

Quote:

On 2002-05-24 18:22, mcfly wrote:
=INDEX(C6:$C$203,MATCH(1,E6:$E$203,0)) this is the formula. I have tried to conditional format but I can't seem to figure it out!

Either keep #N/A's and apply cond format to whiten them, or try the alternative that follows:

=IF(COUNTIF(E6:$E$203,G1),OFFSET($C$1,MATCH(G1,E6:$E$203,0)+CELL("Row",E6)-2,0,1,1),"")

where G1 houses a lookup value like 1. I assumed that you have data after row 203 which is unrelated to the data above that row.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 25th, 2002, 12:22 AM	#1
mcfly Join Date: May 2002 Location: Salisbury N.C. Posts: 158	=INDEX(C6:$C$203,MATCH(1,E6:$E$203,0)) this is the formula. I have tried to conditional format but I can't seem to figure it out!

May 25th, 2002, 12:39 AM	#2
Brian Join Date: Apr 2002 Posts: 113	Your formula works fine, so most likely you are not pointing at the ranges you though you were. #N/A! means that is could not find the data. If that is correct, but you want to display something other than #N/A, the use: =If(isna(INDEX(C6:$C$203,MATCH(1,E6:$E$203,0)), "Not Found",INDEX(C6:$C$203,MATCH(1,E6:$E$203,0)) ) If you thins #N/A is an error, then first test: =MATCH(1,E6:$E$203,0) This is looking for 1 in the range E6:$E$203, which is a little unusual, did you intend to place a cell reference instead of 1? Regards, Brian

May 25th, 2002, 12:57 AM	#3
mcfly Join Date: May 2002 Location: Salisbury N.C. Posts: 158	No, I needed to locate the 1st one(1). I am using this function to place specific text into a specific place in order by the 1st number it comes across. It might be several ones(1) but all I need is the 1st and then I need the 1st two(2). example: 1<-------need this because c=1 1<-------need this because oh=1 2<------need this because c=2 2<------need this because oh=2 and if there is no data in that cell it displays #N/A which I quess I could live with, but-- I don't want to. Thanks!!!!!!!!!!

May 25th, 2002, 01:10 AM	#5
mcfly Join Date: May 2002 Location: Salisbury N.C. Posts: 158	Thank For all the help maybe one day I will finish this project. I Couldn't do it without everyones help.

May 25th, 2002, 01:31 AM	#6
Brian from Maui Join Date: Feb 2002 Posts: 7,599	mcfly, Although I agree with Brian and giacommo, you can hide the #n/a by using conditional formatting. Use =iserror(cell), set font color to match cell color.