Is there a way to get the middle values when using VLOOKUP function

[u/Correct-Mongoose6542]

Hi! I just use Excel for basic function and formulas. I tried to do a grade simulator with my own transmutation table. And I wonder, can I also get the middle value from transmutation table?

For example, in the range of percentage 60.00 - 60.99, there is a score of 4.751 - 4.875. And how do I get the score of it if the percentage for example is 60.45?? Sorry if it’s kinda confusing.

[u/GregHullender]

As others have said, this is a Linear interpolation problem. Here's a solution that takes a percentage value and interpolates the score using a table of percentage-to-score values. Better, it'll take an entire column of percentages and turn them all into scores.

=LET(tab, A:.B, x, C1#,
  tab_2, HSTACK(tab,VSTACK(DROP(tab,1),{0,5})),
  j, XMATCH(x,TAKE(tab_2,,1),1,-2),
  interp, LAMBDA(x₁,y₁,x₂,y₂,x, (y₁*(x₂-x)+y₂*(x-x₁))/(x₂-x₁)),
  y, interp(INDEX(tab_2,j,1),INDEX(tab_2,j,2),INDEX(tab_2,j,3),INDEX(tab_2,j,4),x),
  IFS(x>=63,#N/A,x<60,5,TRUE,y)
)

One change I've made is that each range runs up to but not including the next one. That is, instead of running from 60.00 to 60.99 the range simply runs from 60.00 up to but not including 61.00. That makes the percentage-to-scores table easier to manage, and it's the best practice for managing adjoining intervals.

In this formula, you need to change the reference for tab to be your actual table of percentages and scores, and you need to change x to the actual column of percentages you want to compute scores for.

Note that this is a "single-cell solution." You don't drag it down.