removing #N/A

[adulted]

Hi All

Looking for a way to remove this error when data not found.

Thanks in advance, Paul

fixit test.xls
	C	D	E	F	G
2	end of run review	Die No.	end of run review	2ND DIE IN	BACK UP DIES
3	#N/A	1077B 7	1070 10	1073 15-18	0
4	#N/A	1607A 17-20	1059 29-30	0	1607A 13-16
5	#N/A	1175 11-14	1520C 1-2	0	0
6	1057A 17-18	1057A 17-18	6008A 15	0	0
7	#N/A	1496B 5-6	1147 11-12	0	0
8	#N/A	1056A 19-20	1153 15-20	0	0
9	#N/A	1687A 1	1027 7-8	0	1283B 23-24
10	1153 15-20	1153 15-20	1057A 17-18	0	0
11	#N/A	1504D 3-4	1416A 3-4	0	0
12	1187B 25-26	1187B 25-26	1575A 1-2	0	0
13	#N/A	1533E 5	1187B 23-24	0	0
14	#N/A	1431A 7-8	1187B 25-26	0	0
15	#N/A	1522B 3-4	1682A 1-2	0	0
16	#N/A	1094 5-8	1680A 1-2	0	0
17	#N/A	1016A 46-47	1685A 1	0	0
18	#N/A	1234 37-48	1660A 1-4	0	0
19	1520C 1-2	1520C 1-2	1686A 1-2	0	0
Sheet1

 

[cfree36]

=if(isna(VLOOKUP(D3,$E$3:$E$28,1,FALSE)),"",(VLOOKUP(D3,$E$3:$E$28,1,FALSE)))

Will return a blank when N/A

 

[Pekkavee]

=IF(ISNA(VLOOKUP(D3,$E$3:$E$28,1,FALSE),"",VLOOKUP(D3,$E$3:$E$28,1,FALSE)))

Pekka

 

[Crow_23]

    Selection.AutoFilter
    Selection.AutoFilter Field:=3, Criteria1:="#N/A!"
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents

That would be the macro route. Other way would be to simply Copy and Paste Special as a Value to rid yourself of the formula. Then you could do a Replace (Ctrl + H) and just type in #N/A in the first box and a space in the second box.

 

[cfree36]

Pekka... missing )

 

[adulted]

Great

Goodness me, you's are the best.

 

[Aladin Akyurek]

Invoke: Either...

=IF(ISNUMBER(MATCH(D3,$E$3:$E$28,0)),D3,"")

or...

=CHOOSE(ISNUMBER(MATCH(D3,$E$3:$E$28,0))+1,"",D3)

instead of:

=VLOOKUP(D3,$E$3:$E$28,1,FALSE)

 

[Pekkavee]

Pekka... missing )

OOOPS

Pekka

 

[Pekkavee]

Aladins formulas are the best.

I have to study them and learn from them.

Pekka

 

[mark.wagner]

ah grasshoppa....you can learn much from the mastah.....

I, too shall learn from Aladin's response....