Excel Exercise for School Based Job

jayu89

New Member
Joined
Jul 15, 2020
Messages
17
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Hi All,

Just joined the forum and the wealth of knowledge among the members, is extraordinary!
Which brings me onto a thread of my own to ask for some help:

I have been trying to apply for jobs in school as I worked in an Admin assistant and exams officer at a sixth form. However, the last 10 years I have been in retail management and now want to return back into the education sector. Anyway, over the lock down, I have applied for a number of roles and went as far as being shortlisted and progressed to interview. However, prior to the interview, the school sends out a few tasks to complete prior to interview and in almost all cases, there is an excel exercise. Recently, I did an exercise, but was so lost as to what functions to use, that I had to do the exercise manually and did not finish. After, I realised it was to do with vlookup and IF functions (or correct me if I'm wrong). Anyway, a few more applications that I had applied to, have got back to me yesterday and today for interview, and all 3 have said that there is an exercise, and I want to be ready if it is an excel exercise.

So in preparation, I was trying to do the excel task recently sent, it's a simple table with what the science department of a school entered for students and then another table beside, on the exam entries made to exam board. The exercise is:

On the excel spreadsheet there is an entry sheet received from the Science department asking you to confirm the pupils have been entered for the correct exam.


"As you will see, there are three possible tiers for this exam:

• Higher – Exam code – A217H.
• Intermediate – Exam code – A217I
• Foundation – Exam code – A217F

There is also a report printed from the Academy’s exam system confirming the actual entries sent to the exam board.

Please cross-check the two documents and identify whether pupils have been entered in accordance with the Science department’s wishes. Highlight in yellow any mistake or queries you might wish to discuss, and leave additional comments that explain what your queries are."


Here is a screenshot of the worksheet:

LH9CFf3.jpg


I attempted to do the exercise today. This is what I did:

1. In Column H, entered the following formula: =VLOOKUP(E4, examcodes,2,FALSE), examcodes is the small table in columns Q and R. My thinking was that this will help check whether the students were entered for the correct module. See below:

voKRu26.jpg


2. To check students name from Science dept list with exam board entries, I merged the first and last name to match the exam board entries name list, by using the following formula: =CONCATENATE(B4, " ",A4), in column I. See below:

BC7eOfi.jpg


3. In column J, I then applied the formula: =VLOOKUP(I4,$L$4:$L$40, 1, FALSE), to cross-check the names from Science dept list against exam board entries. This produced NA for non-matching records. See below:

xLn65jc.jpg


My questions are the following:
1. Is there a way of checking between the 2 tables in one go? And highlighting the records of name that do not appear in either table? For example, some records in exam entries table, use short hand names, like Jim instead of James, or Beth instead of Elizabeth.
2. How to check whether each student has been entered into the correct tier paper by name? I cannot figure this bit out and it is frustrating me, and feel like this is where the IF function comes into play.
3. Forgive me, but is there any better way to extract the data as required by the task?

I have not used excel in over 15 years and do not know where to start!
Thank you in advance ALL.

Jay
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,347
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
You could put this in H4 copied down
=IFERROR(IF(INDEX($M$4:$M$40,MATCH(B4&" "&A4,$K$4:$K$40,0))=INDEX($Q$4:$Q$6,MATCH(E4,$P$4:$P$6,0)),"OK", "Error"),"Not found")
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,234
Office Version
  1. 365
Platform
  1. Windows
Jay

So you need to check if the code in column K corresponds to the 'level' in column E?
 

jayu89

New Member
Joined
Jul 15, 2020
Messages
17
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Hi Fluff

Thank you for replying to my thread, appreciate it.
Can I pick your brain and ask what this formula does?

Thanks,
Jay

Hi & welcome to MrExcel.
You could put this in H4 copied down
=IFERROR(IF(INDEX($M$4:$M$40,MATCH(B4&" "&A4,$K$4:$K$40,0))=INDEX($Q$4:$Q$6,MATCH(E4,$P$4:$P$6,0)),"OK", "Error"),"Not found")
 

jayu89

New Member
Joined
Jul 15, 2020
Messages
17
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

He
Jay

So you need to check if the code in column K corresponds to the 'level' in column E?

Hey Norie,

Thanks for posting.
That is correct, from first screenshot provided, need to check K corresponds to E (there are atleast 2 mistakes when I did it manually) and also check the names are correct and if any students have been omitted etc (again, a few mistakes definitely with names and students not being entered when I did it manually).

Thanks Norie,
Jay
 

jayu89

New Member
Joined
Jul 15, 2020
Messages
17
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
=IFERROR(IF(INDEX($M$4:$M$40,MATCH(B4&" "&A4,$K$4:$K$40,0))=INDEX($Q$4:$Q$6,MATCH(E4,$P$4:$P$6,0)),"OK", "Error"),"Not found")

Hey Fluff,
I also pasted the code into H4, unfortunately it gives me an error.
Let me know if you find the error.
Thansk alot.
Jay
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,347
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Cross posted Excel Exercise for School Based Interview

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

jayu89

New Member
Joined
Jul 15, 2020
Messages
17
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Cross posted Excel Exercise for School Based Interview

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
Apologies, I did not think the were related. But posted elsewhere, and now that I know to link them, better. Over there, there is the excel worksheet attached. May be easier to work on it.

Thank you and sorry for not following rules.
Jay
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,347
Office Version
  1. 365
Platform
  1. Windows
Ok, I forgot to mention the formula in post#2 needs to be confirmed with Ctrl Shift Enter, not just Enter, then copied down.
 

jayu89

New Member
Joined
Jul 15, 2020
Messages
17
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Ok, I forgot to mention the formula in post#2 needs to be confirmed with Ctrl Shift Enter, not just Enter, then copied down.
Hey Fluff

Sorry to ask, which screenshot are you working from above? From the first screenshot to 4, I have added extra columns when I was trying to do it myself.
Thanks,
Jay
 

Watch MrExcel Video

Forum statistics

Threads
1,123,453
Messages
5,601,755
Members
414,471
Latest member
cchunyadi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top