Interpolate Between Data Series in Chart Using Y-Axis Axis to Get Value on X-Axis

Good day to all! I am trying to solve for a value in a chart by interpolating between multiple data series. Each data series is a curve for a specific number; in this case TDH (ft). My lookup values would be Q (gpm) which values are the Y-axis and TDH (ft) which are the individual data series. I am needing to solve for P (W) which would be value on the X-axis. I have attached a copy of the spreadsheet to better explain what I am after.

What you'd need to do is set up Polynomial regression Eq for each series. Then use Excel's Goal seek to solve for "x".

Just a simple example using 2nd order polynomial.

For TDH = 215 (since it's closest to TDH of 209 in available series).
y = -3.15672E-06*x^2+0.019072*x-6.28348

This can be derived by using LINEST function. See attached for how it's set up for 2nd order.
Linest_215.JPG

Formula in U22: =Q22*T22^2+R22*T22+S22

Then go to Data tab, Forcast section, "What-If Analysis" and select "Goal Seek".
Set up like below.
Goal_Seek.JPG

Then hit Ok. You will see 1330.18 as result for x in T22.

Note: You may find below link useful.
http://stackoverflow.com/questions/1...ssion-in-excel

See attached.

Forgive me for the delayed response. Both recommendations were good but was needing something that could be automatically updated when the user input is changed. I think that I may have come up with something pretty close to what I was needing. See attached sheet.