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

1 test1 3 test2 5 test3 8 test4 11 test5 13 test6 15 test7 <tbody> </tbody>

<tbody>
</tbody>

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.