Vlookup and/or Index Help

[hajiali]

Hey everyone in Column A I have some random numbers and in Column C I have The missing numbers From Column A. Ex: [TABLE="width: 500"]
<tbody>[TR]
[TD]2[/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]#N/A[/TD]
[TD]1[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD][/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]#N/A[/TD]
[TD]5[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]

Using SMALL(IF(COUNTIF($A$1:$A$130,ROW($1:$130))=0,ROW($1:$130),""),ROW(A1)) IN COLUMN C1 and dragdown

What I need done in Column B is if anywhere column A=ISNA than B to pull the value in C using the above ex. B6=1 and B8=5. Also if the value of A3 goes from 4 to #N/A than column C does give me 1,4,57,8 etc using the formula above so therefore the value of B3=1, B6=4, and B8=5

I think this needs an if with index formula or vlookup but I don't know how to put it together. any help and/or ideas would be greatly appreciated

 

[jtakw]

Hi,

Would this work for you:

Book1
	A	B	C
1	2		1
2	3		4
3	#N/A	1	5
4	6		7
5	9		8
6	#N/A	4	10
7	14		11
8	#N/A	5	12
9	18		13
Sheet525

Cell Formulas
Range	Formula
B1	=IF(ISNA(A1),INDEX(C$1:C$9,COUNTIF(A$1:A1,#N/A)),"")

Change/adjust cell references/range as needed. Formula copied down.

 

[hajiali]

JTAKW This works great I really appreciate it

 

[jtakw]

Hmmm. no, my formula will Count Real #N/A errors.

EDIT: See your edit above.

You're welcome.