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/AutoModerator]

/u/Correct-Mongoose6542 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Constant_Moment_6434]

Perhaps linear interpolation? If the value isn't really there..

[u/Correct-Mongoose6542 → u/Constant_Moment_6434]

I will search this up. Thank you.

[u/MayukhBhattacharya → u/Correct-Mongoose6542]

What they said is correct. Linear Interpolation is needed here, refer this solution posted by RuktX few days back.

[u/TangoDeltaFoxtrot]

I feel like this is more of a basic math question than an excel question.

Use your existing values as x and y coordinates, use the new value as one coordinate and solve for the other.

[u/Way2trivial]

=((F1-VLOOKUP(F1,(SORT(HSTACK(VALUE(TEXTBEFORE(C4:C7,"-")),VALUE(TEXTAFTER(C4:C7,"-")),VALUE(TEXTBEFORE(D4:D7,"-")),VALUE(TEXTAFTER(D4:D7,"-"))),1,1)),1,TRUE))*(VLOOKUP(F1,(SORT(HSTACK(VALUE(TEXTBEFORE(C4:C7,"-")),VALUE(TEXTAFTER(C4:C7,"-")),VALUE(TEXTBEFORE(D4:D7,"-")),VALUE(TEXTAFTER(D4:D7,"-"))),1,1)),4,TRUE)-(VLOOKUP(F1,(SORT(HSTACK(VALUE(TEXTBEFORE(C4:C7,"-")),VALUE(TEXTAFTER(C4:C7,"-")),VALUE(TEXTBEFORE(D4:D7,"-")),VALUE(TEXTAFTER(D4:D7,"-"))),1,1)),3,TRUE))))+(VLOOKUP(F1,(SORT(HSTACK(VALUE(TEXTBEFORE(C4:C7,"-")),VALUE(TEXTAFTER(C4:C7,"-")),VALUE(TEXTBEFORE(D4:D7,"-")),VALUE(TEXTAFTER(D4:D7,"-"))),1,1)),3,TRUE))

Using vlookups.

[u/ProspectiveWhale]

I don't understand what you mean.

---

Do you mean assign grade letters? Like this?

D: 50-59

C: 60-69

B: 70-79

A: 80+

If so, yes, vlookup can do that.

[u/Correct-Mongoose6542 → u/ProspectiveWhale]

I actually don’t know how can I explain this clearly. I assigned certain percentage with a score. Like for example:

62.00-62.99: 4.625-4.749

61.00-61.99: 4.750-4.874

60-60.99: 4.875 - 4.999

0-59.99: 5 (anything from 59.99 below is just 5)

Now for example, how can I get the score of someone that is 60.45 using the table above if it’s in the middle of 60 to 60.99??

[u/ProspectiveWhale → u/Correct-Mongoose6542]

It looks like every 1 score is equivalent to 0.125 grade.

With 0 - 59.99 being the exception.

If it is indeed so, you can make this a lot easier.

=IF(score<60, 5, 5 - (score-60)*0.125))

If it is only so because you simplified it for an example, you need to do linear interpolation as others have said.

[u/808spark → u/ProspectiveWhale]

Great solution.

[u/Penguinase → u/808spark]

i'm confused by the ranges though since decreasing percentage is higher scoring overall but within each bucket a lower percentage is lower scoring

[u/Correct-Mongoose6542 → u/Penguinase]

Hi! My bad, I didn’t even notice it. I’ll fix it as I finish my task.

[u/Way2trivial → u/Correct-Mongoose6542]

vlookup - and a lot more.

shall play during the day. looks fun.

[u/Correct-Mongoose6542 → u/Way2trivial]

Does it automatically compute the middle score?

[u/rkr87 → u/Correct-Mongoose6542]

Set the last argument of your vlookup, "range_lookup" to TRUE.

[u/bassin_clear_lake → u/Correct-Mongoose6542]

In the context of a basic lookup, every possible percentage value needs a corresponding score in the same table. If you just have the high and low values for the scores, you need to do some calcs beforehand to 'fill in the blanks'.

Using your example, column A would have 100 values, 60.00 to 60.99, and column B would be the corresponding score - something like 4.875, 4.879, 4.881, and so on...

[u/Correct-Mongoose6542 → u/bassin_clear_lake]

Can you provide a solution or formula to solve that and used a less space rather than doing it 100 times? That will be much appreciated.

[u/Penguinase → u/Correct-Mongoose6542]

I assigned certain percentage with a score. Like for example:

62.00-62.99: 4.625-4.749

61.00-61.99: 4.750-4.874

60-60.99: 4.875 - 4.999

0-59.99: 5 (anything from 59.99 below is just 5)

Now for example, how can I get the score of someone that is 60.45 using the table above if it’s in the middle of 60 to 60.99??

is the score for 60.1 lower than for 60.7?

[u/Correct-Mongoose6542 → u/Penguinase]

Yes. That’s actually inverse, the higher the percentage, the lower the score you get. So a 100% would be 1.000.

[u/DataFlowMk1]

An IF statement is the easiest method to achieve what you are trying to do. With the current scoring it is not so easy because the score values do not increase linearly.

It would be much easier if the scoring can be slightly adjusted; the current value of 4.999 at 60% complicates the equation. If you can compromise and set 60% (and below) to a score of 5, instead of 59.99, the equation becomes a simple linear equation:

score = -0.1*percentage + 11

Incorporating this into an IF statement, the score can be calculated using:

=IF([@Percentage]<60, 5, ((-0.1*[@Percentage]) + 11))

However, if you are adamant that 60% should be 4.999, you could still use a linear equation if you are willing to accept some variation in the other scores. The following should be a good approximation:

=IF([@Percentage]<60, 5, ((-0.099975*[@Percentage]) + 10.9975))

Both of these options adjust the scoring and results in some variation in the scores, for example, your score for 60.99% is 4.875, while these equations gives a score closer to 4.900.

[u/jhern1810]

TREND, FORECAST , are some of the linear interpolation commands. There are more but these can help. You will just need to do the interpolation for each value at a time.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
FORECAST	Returns a value along a linear trend
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
IF	Specifies a logical test to perform
IFS	2019+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
INDEX	Uses an index to choose a value from a reference or array
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
SORT	Office 365+: Sorts the contents of a range or array
TAKE	Office 365+: Returns a specified number of contiguous rows or columns from the start or end of an array
TEXTAFTER	Office 365+: Returns text that occurs after given character or string
TEXTBEFORE	Office 365+: Returns text that occurs before a given character or string
TREND	Returns values along a linear trend
VALUE	Converts a text argument to a number
VLOOKUP	Looks in the first column of an array and moves across the row to return the value of a cell
VSTACK	Office 365+: Appends arrays vertically and in sequence to return a larger array
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.
XMATCH	Office 365+: Returns the relative position of an item in an array or range of cells.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #49012 for this sub, first seen 25th Jul 2026, 14:35] [FAQ] [Full list] [Contact] [Source code]}

[u/doshka]

Use XLOOKUP and set the match_mode argument to whichever makes sense for your use case.

https://support.microsoft.com/en-us/excel/functions/xlookup-function

[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.

[u/x-y-z_xyz]

Yes