Look up missing number and apply to where #N/A

[hajiali]

Table 1

Table 2

Table 3

<tbody>
</tbody>

	DL	DM	DN		DL	DM	DN		DL	DM	DN
1	SHIFT				SHIFT				SHIFT
2	1		16		1		16		1		16
3	2		17		2		17		2		17
4	#N/A	16	18		#N/A	18	18		#N/A	16	18
5	3		19		3		19		3		19
6	#N/A	17	24		#N/A	24	24		#N/A	16	24
7	#N/A	18	27		#N/A	27	27		#N/A	16	27
8	#N/A	19	28		#N/A	28	28		#N/A	16	28
9	#N/A	24	29		#N/A	29	29		#N/A	16	29
10	#N/A	27	30		#N/A	30	30		#N/A	16	30
11	5		31		5		31		5		31
12	20		32		20		32		20		32
13	#N/A	28	33		#N/A	33	33		#N/A	16	33
14	6		34		6		34		6		34
15	4		35		4		35		4		35
16	#N/A	29	36		#N/A	36	36		#N/A	16	36
17	21		37		21		37		21		37
18	23		38		23		38		23		38
19	#N/A	30	39		#N/A	39	39		#N/A	16	39

<tbody>
</tbody>

Hey everyone my plan here is to find the missing numbers in Column DL and place the missing number anywhere that #N/A is in DL into column DM as shown in the table 1 Far Left above. I was able to figure out how to find the missing Number which is in column DN just for visual purpose.

in DM2= IF(ISNA(DL2)),SMALL(IF(COUNTIF($DL$1:$DL$130,ROW($1:$130))=0,ROW($1:$130),""),ROW(DL1))," ")

That will give me the first missing numbers in DM2 is 16, however, if DL2 and DL3 are not "#N/A" as shown above it puts 18 in DL4 instead of the first missing number 16. with above eq it puts the # in DN adjacent to DM ex DM3=DN3, DM4=DN4, DM5=DN5...ETC as shown in the Middle table Table 2 above. it does not give me the first missing number as it should. If i put $ sign at the end of the eq. ", ROW(DL$1, " ") and then drag in DM it gives me First missing number 16 in All column in DM as shown in the Far Right table 3. Any suggestion is greatly appreciated if you have any question please dont hesitate to ask.