How to Find Intersection of Two Lines in Google Sheets

You can use the following formulas to find the point of intersection of two straight lines in Google Sheets:

Find the X-Value of Intersection:

=(INTERCEPT(y2,x2)-INTERCEPT(y1,x1))/(SLOPE(y1,x1)-SLOPE(y2,x2))

This formula assumes x1 and x2 represent an array of x-values for each line and y1 and y2 represent an array of y-values for each line.

Once you find this x-value, you can then plug that value into this formula to find the y-value of intersection:

Find the Y-Value of Intersection:

=SLOPE(y1,x1)*x_intercept+INTERCEPT(y1,x1)

The following step-by-step example shows how to use these formulas in practice.

Step 1: Enter the Values for Each Line

First, let’s enter the (x, y) coordinates for two lines:

Next, type the following formula into cell B10 to find the x-value of intersection:

=(INTERCEPT(E3:E6,D3:D6)-INTERCEPT(B3:B6,A3:A6))/(SLOPE(B3:B6,A3:A6)-SLOPE(E3:E6,D3:D6))

The following screenshot shows how to use this formula in practice:

The x-value of intersection turns out to be 1.5.

Next, type the following formula into cell B11 to find the y-value of intersection:

=SLOPE(B3:B6,A3:A6)*B10+INTERCEPT(B3:B6,A3:A6)

The following screenshot shows how to use this formula in practice:

The y-value of intersection turns out to be 3.

If we plot each line on the same plot, we can see that the intersection point is indeed at the (x, y) coordinates of (1.5, 3):

This represents the point on the plot where the two lines intersect.

The following tutorials explain how to perform other common tasks in Google Sheets: