How to find the nearest value of A from within C and place a copy of that value in B?

this may sound dull to you experts but i have no expertise whatsoever in Excel and am hoping you can help me..

I have the following:

COLUMN A (A1:A100) FULL OF VALUES

COLUMN B (B1:B100) EMPTY

And COLUMN C (C1:C30) FULL OF VALUES

I am trying to do the following:

To find the nearest value to A1 from within C1:C30 and then copy that nearest value (a value coming from C1:C30 that is nearest to A1)into B1 and so forth until B100..
----- I am sorry if i didn't explain it clearly but please let me know if so..

For Example:

1- if A1=6 and C1:C3=1,2,5;

2- The nearest numerical value to A1 would be 5

3- 5 would be placed in B1

thanks in advance

Try,

First one will choose the nearest value higher than A2 & Second one will choose nearest value lower than A2

Both were array formulas need to be entered with CTRl+SHIFT+ENTER and drag it down

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.