Counting text that appear less/more than another, using VLOOKUP

I'm a teacher, too, and have done this myself, so will you please attach a sample Excel workbook? Nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

How do values like P8 fit in? They need to be on your grade lookup table. Please provide an amended version of the file.

Are you happy to use helper columns?

Consider to input the desired results so that we don't have to second-guess if what we are doing is correct or not. you may look at my signature to upload an eg that is easier to understand.

For instance, tell us what we should expect to see in cell M39. It is for Below Expected, Y2. Y2 expectations in cell M35 shows 2C. and in Grade Table worksheet, 2C is equivalent to 16 points. So is it to find how many of them are below 16 points? There are two instances of grade 1A that is below 16 points. If so, try:

Formula:

Please Login or Register  to view this content.

for the expected row, it should be straightforward like what you did? COUNTIF the range is EQUALS to the grade itself.

and for Above Expected row (cell M41), change the signs for the 1st formula from lesser than to Greater than:

Formula:

Please Login or Register  to view this content.

@Jones90:
i intentionally ignored your question on post #8 previously. we are volunteering contributors here who don't earn any income from helping you. all that we are requesting from you is some form of:
i) appreciation and ;
ii) effort in helping us to understand your problem better.

you thanked me in post #8 and #9, so that's good. but how about answering post #6?

and where is your effort shown to help us understand your problem better? i mentioned:

Consider to input the desired results so that we don't have to second-guess if what we are doing is correct or not. you may look at my signature to upload an eg that is easier to understand.

For instance, tell us what we should expect to see in cell M39. It is for Below Expected, Y2. Y2 expectations in cell M35 shows 2C. and in Grade Table worksheet, 2C is equivalent to 16 points. So is it to find how many of them are below 16 points?

you knew how to quote my post, but yet did not bother to read or do them.
1. where is the cell you are expecting your answer in? i am guessing M45 for Boys.
2. what is the answer you are expecting? i am guessing 1. Only 1 is below 16 (Y2 expectation result). that is Pupil B.

as you can see, i am guessing. if i guessed it right, awesome. if i guessed it wrongly, i have wasted both our time and have to spend more time answering your next question with elaboration.

Formula:

Please Login or Register  to view this content.

i added the part in red. change it to "G" for Girls. if i got it right, please refer to my signature on how to close the thread. if i got it wrong, i am out.