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]

Aladin

There is text in some of the values... and, based on the fact that they are positioned to the left of the cell... I would assume they are text.

I am not real familar with your recommendation... but would assume that isnumber would not work if the data was text?

 

[cfree36]

Aladin:

Much of the data has text... and is positioned on the left side of the cell... so I would assume it is formatted as text.
Would the isnumber formula work in that case?

 

[cfree36]

Sorry for the double reply... didn't see there were two pages... and didn't think my first question worked.



And, I am not in any way trying to be critical... Aladin is awesome... just trying to better understand the formula as well.

 

[Aladin Akyurek]

...just trying to better understand the formula as well.
...

MATCH() always returns a position indicating integer if successful, otherwise #N/A. ISNUMBER() tests whether the result MATCH returns is a number. Hence the data type of the lookup value (text, number, date, time, truth value) MATCH is fed with is irrelevant.