Returning two similar values from two different columns

[Aled87]

Hi guys,

Basically i have a set of data plotted on an x-y scatter plot where at
single point the two curves cross over each other and meet at a singl
point, i.e. 'x and y values are both the same where they cross.

Im trying to determine this operating point by generating lookup table
for both each curve.,

for example in the x axis i need to match the same x value for one curv
'1st lookup table row' with the same x value of the other curve '2n
lookup table row'. But the corresponding y values also need to b
matching or relatively close to find the right point. Any ideas ho
this can be achieved, i was thinking of using a VLOOKUP and INDE
functions but it's much more complicated than that.

Any advice would be great

 

[merjet]

This might help. Suppose the source of the two lines is Range("B2:C25") on Sheet1.

Sub FindCrossover()
Dim dDiff(2 To 25) As Double
Dim dMin As Double
Dim iCt As Integer
Dim iX As Integer
For iCt = 2 To 25
dDiff(iCt) = Sheet1.Cells(iCt, "B") - Sheet1.Cells(iCt, "C")
Next iCt
For iCt = 3 To 25
If (dDiff(iCt) >= 0 And dDiff(iCt - 1) < 0) _
Or (dDiff(iCt) <= 0 And dDiff(iCt - 1) > 0) Then
'found crossover
iX = iCt
dMin = Abs(dDiff(iCt))
If Abs(dDiff(iCt - 1)) < Abs(dDiff(iCt)) Then
dMin = dDiff(iCt - 1)
iX = iCt - 1
End If
Debug.Print iX, Sheet1.Cells(iX, "B"), Sheet1.Cells(iX, "C")
Exit For
End If
Next iCt
End Sub