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.
