vba match a range of numbers

[monsierexcel]

hey guys, i have a VBA coding problem and i am not sure if i need to write in index/match formula into my vba or if theres a better way to build this.

I have a list of numbers
[TABLE="width: 500"]
<tbody>[TR]
[/TR]
[TR]

[/TR]
[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]1
[/TD]
[TD]test1
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]test2
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]test3
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]test4
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]test5
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]test6
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]test7
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

SO with this table iif my value produces "6" i want it to produce test 3 - as 5 is the nearest
I think its index/match i require but not sure how to write it in VBA?

thanks guys

 

[Scott Huish]

This would do it as a formula (with CTRL-SHIFT-ENTER). Are you writing the value back to the sheet in your VBA code or what are you trying to do with that?

Excel 2010
	A	B	C	D	E
1	1	test1			6
2	3	test2			test3
3	5	test3
4	8	test4
5	11	test5
6	13	test6
7	15	test7
Sheet1

Cell Formulas
Range	Formula
E2	{=INDEX(B1:B7,MATCH(MIN(ABS($E$1-A1:A7)),ABS($E$1-A1:A7),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.