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?

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

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

Author Commented:
Thank again!
0

Excel & VBA ExpertCommented:
You're welcome! Glad it worked as desired.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.