Excel - How to Use a VLOOKUP Formula - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need a formula (I think VLOOKUP) that will enable me to: Use data from one column, compare with another and tell me the difference. Im a teacher and want to work out the amount of marks needed to the next grade boundary. I've created the rest, but this has me stumped!
Solved by F. A. in 28 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 08/05/2018 - 08:40
Hi
Excelchat Expert 08/05/2018 - 08:40
Welcome to Got IT Pro.
Excelchat Expert 08/05/2018 - 08:40
Share the sample in preview.
User 08/05/2018 - 08:41
I don't know what you mean...but essentially...
Excelchat Expert 08/05/2018 - 08:42
Ok. I am referring the sample.
User 08/05/2018 - 08:42
in the column I have labelled 'to next mark' I want to know, how many marks it is until the student reaches the next grade...
User 08/05/2018 - 08:42
sheet 2, has this information with where the grades are
Excelchat Expert 08/05/2018 - 08:42
Sure. I got it.
User 08/05/2018 - 08:43
So how do I do that please?
Excelchat Expert 08/05/2018 - 08:43
I am working on the formula.
User 08/05/2018 - 08:44
Ok thank you
User 08/05/2018 - 08:47
and so that if I change the 117 to 118, the number will automatically update to the right number :) Thank you
Excelchat Expert 08/05/2018 - 08:48
=index(Sheet2!A:A,match(B2,Sheet2!A:A,1)+1,1)
Excelchat Expert 08/05/2018 - 08:48
I have used match and index formula to get the next highest number.
User 08/05/2018 - 08:50
It doesn't like it when I copy it over and amend...
Excelchat Expert 08/05/2018 - 08:50
Please check it.
Excelchat Expert 08/05/2018 - 08:51
I have copied the formula to the next cell. It is picking up the right number.
User 08/05/2018 - 08:51
the B2 in the formula, is that on sheet 1 or 2?
Excelchat Expert 08/05/2018 - 08:51
In Sheet1.
User 08/05/2018 - 08:51
and A:A is that the whole of column A?
Excelchat Expert 08/05/2018 - 08:51
That is referring current matk.
Excelchat Expert 08/05/2018 - 08:52
And A:A is used in general for the whole column in sheet 2
User 08/05/2018 - 08:53
Yes I see what you have done and it works, however I need to next mark to say 3...
User 08/05/2018 - 08:53
tells me the difference
User 08/05/2018 - 08:53
so it tells me...
Excelchat Expert 08/05/2018 - 08:53
You want the grade?
User 08/05/2018 - 08:54
no, I want 'to next mark' to tell me the difference between the '117' and the next mark up eg...to next mark would then say '3' in the row
Excelchat Expert 08/05/2018 - 08:55
Ok
Excelchat Expert 08/05/2018 - 08:55
=index(Sheet2!A:A,match(B2,Sheet2!A:A,1)+1,1)-B2
User 08/05/2018 - 08:55
Thank you!
Excelchat Expert 08/05/2018 - 08:55
Just subtracted B2.
Excelchat Expert 08/05/2018 - 08:55
You are welcome.
Excelchat Expert 08/05/2018 - 08:56
Please do visit Got It Pro.
Excelchat Expert 08/05/2018 - 08:56
Have wonderful day ahead.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.