formula to index

[refaun]

Hi there.
I have list of two columns random number with random distance between them.i want formula in column C :
1. find nearest value from Col.A and put it on =C1
2. find nearest value from Col.B while his ROW be bigger than Previous value(step 1)=C2
3. find nearest value from Col.A while his ROW be bigger than Previous value(step 1)=C3
4. and so on...

q.png

In other words, start from col.A , find nearest value and ignore all blew number until the col.B have a value; then reverse the process.


I'm trying find nearest value with this code

Please Login or Register  to view this content.

But i have no idea to how can I limit Indexing to Previous step.

I'm not even sure that my description is the best way to solve this problem. In attached file specified wanted cell with red color.

Any help is appreciated.

[XLent]

I would be inclined to simplify, a little, by identifying the rows in one column, and then just use INDEX etc to retrieve the associated values in another, so:

Please Login or Register  to view this content.

the above would return 1,3,4,7,5,1,8,9 -- which I believe to be correct based on fact these are red in your file

[refaun]

simple,pretty and easiest way! thank you XLent