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
biker9Asked:
Who is Participating?
 
Subodh Tiwari (Neeraj)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))

Open in new window

0
 
Subodh Tiwari (Neeraj)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))

Open in new window

1
 
Ejgil HedegaardCommented:
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))

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
biker9Author Commented:
Thank you, works great!
0
 
biker9Author 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
 
biker9Author Commented:
Thank again!
0
 
Subodh Tiwari (Neeraj)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.

All Courses

From novice to tech pro — start learning today.