Find nearest values in two columns

Hello Experts!

I am looking for an Excel formula (or VBA) that can determine where two columns of values might meet in terms of approximate values. Column A will most likely (but not always) decrease in value over time, and Column B will most likely (but not always) increase in value over time. The formulae (or VBA) will return the year in which the two columns intersect.
Thank you,

Biker9
Two-columns.xlsx
Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Excel & VBA ExpertCommented:
Maybe this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

``````=INDEX(C8:C38,MATCH(MIN(ABS(D8:D38-E8:E38)),ABS(D8:D38-E8:E38),0))
``````
1
Commented:
With 2 Index functions for the Abs part, it can be a normal formula.
``````=INDEX(C8:C38,MATCH(MIN(INDEX(ABS(D8:D38-E8:E38),,)),INDEX(ABS(D8:D38-E8:E38),,),0))
``````
0
Author Commented:
Thank you, works great!
0
Author Commented:
Sorry about this, but I should have indicated that the columns are actually to row 84, and both solutions offered return zero if there are no values in the rows 39  to 84. So for this to work for me, we need to find nearest values in row 8 to row 84, other than zero.
0
Excel & VBA ExpertCommented:
No problem. Try this Array Formula which requires confirmation with Ctrl+Shift+Enter.

``````=INDEX(C8:C84,MATCH(SMALL(IF(D8:D84<>"",ABS(D8:D84-E8:E84)),COUNTIF(D8:D84,0)+1),ABS(D8:D84-E8:E84),0))
``````
0

Experts Exchange Solution brought to you by