Karmond,
That function will allow you to interpolate if you already know what x1, x2, y1, and y2 are. The original question (I think) was referring to a situation where x1, x2, y1, and y2 are in a list of values, and need to be identified automatically for a given x3.
Greg,
Thanks for the info on Vlookup. That's a very useful worksheet function. I can't figure out how to make it cough up the value one step larger than the search value though (it defaults to the next smaller value). Thanks for showing me that one though, I'll definitely be using it in the future.
Here's another solution that I came up with (inspired by Greg's post):
if we have a spreadsheet where
* column A contains known x values
* column B contains known Y values
* col D contains X values for which a Y value is desired
* values in column x are in ascending order
* cols A, B, and D have data in rows 2 thru 555
then:
=match(D2,$A$2:$A$555,1) gives the row number within 2-555 where the X value just lower than the X value in D2 is located (we'll store this value in E2)
=INDEX($A$2:$A$555,E2,1) gives the value of "lower X" (we'll store it in F2)
=INDEX($A$2:$A$555,E2+1,1) gives the value of "upper X" (we'll store it in G2)
=INDEX($B$2:$B$555,E2,1) gives the value of "lower Y" (we'll store it in H2)
=INDEX($B$2:$B$555,E2+1,1) gives the value of "upper Y" (we'll store it in I2)
=(D2-F2)/(G2-F2)*(I2-H2)+H2 gives us the value we are looking for.
alternatively, we could condense all of those equations into one bulky one:
=(D2-INDEX($A$2:$A$555,match(D2,$A$2:$A$555,1),1))/(G2-INDEX($A$2:$A$555,match(D2,$A$2:$A$555,1),1))*(INDEX($B$2:$B$555,match(D2,$A$2:$A$555,1)+1,1)-INDEX($B$2:$B$555,match(D2,$A$2:$A$555,1),1))+INDEX($B$2:$B$555,match(D2,$A$2:$A$555,1),1)
whew, that's a mouthful.