In this post, we use the INDEX and MATCH functions together to determine a dependent value from a given independent value. We use a list of x (independent) and y (dependent) values as shown below.
The user will then enter an independent (x) value in cell C2.
To compute the dependent value, we will need to extract the x values both below and above the x value in cell C2 along with the associated y values. Recall that the INDEX function returns a value while the MATCH function returns a position. In the figure below, we begin with the INDEX function since we ultimately want to return a value. For the x values, we want to return a value from column A so we use the array A2:A11. For the row number, we are trying to match the value in cell C2 with a value in the array A2:A11. We choose a match type of 1 to obtain the value below our value in cell C2.
To get the x value above our value in cell C2, we simply add 1 to the row number obtained from our MATCH function. This is shown below.
To obtain our y values, the same formula is used except that the array used for the INDEX function is B2:B11. Getting the y values below and above our associated x value is shown in the next two figures below.
Now that we have the x and y values that bound our given independent value, we can interpolate to get our dependent value. We simply figure out where the given independent value lies between the bounding x values, and then apply that to the bounding y values. This computation is shown below.
Comments
Post a Comment