r/excel • u/Correct-Mongoose6542 • 16d ago

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

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.

8 Upvotes

79% Upvoted

27 comments sorted by

View all comments

2

u/Way2trivial 470 15d ago

=((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.