Please help me with these 3 vlook up problems. Excel Sheet is attached.

[fahadferoz01]

Question 1:
Springfield University has over 5,000 students. Each student is assigned a four-letter Status, which is either UNCL (unclassified), UGRD (undergraduate), or GRAD (graduate). The first digit of the student number reflects the four-letter Status:

• If the first digit of the student number is 1 or 2, then the Status is UNCL.
• If the first digit of the student number is 3, 4, or 5, then the Status is UGRD.
• If the first digit of the student number is 6, 7, 8, or 9, then the Status is GRAD.

No student has a student number beginning with the number 0. Furthermore, each student number consists of exactly six digits. Assume for the purposes of the following three questions, and for simplicity, that a student cannot change Status.

Professor Grey is an instructor of COMM 499, an undergraduate course at the Faculty of Commerce at Springfield U that is open to all students regardless of their status. This means that even students with GRAD and UNCL status (in addition to those with UGRD status) are allowed to take the course. The grading system used for UNCL and UGRD students, however, is different from the grading system used for GRAD students.

First, Professor Grey needs to complete column B of his class list (in an Excel spreadsheet) to assign each student to the correct four-letter Status. He wants to use a VLOOKUP function. Professor Grey intends to type up the formula in cell B2, and then drag the formula down to cell B15. The lookup table will be in F3:G5.

Complete the lookup table F3:G5.

Question 2:
Assume that Professor Grey has completed the lookup table F3:G5 correctly.

Recall that Professor Grey needs to complete column B of his class list (in an Excel spreadsheet) to assign each student to the correct four-letter Status. He wants to use a VLOOKUP function. Professor Grey intends to type up the formula in cell B2, and then drag the formula down to cell B15.

Complete the formula in cell B2. Remember that the formula, when dragged down to cell B15, must work automatically without the need to manually edit it.

Question 3:
Now, Professor Grey wants to assign the letter grade for each student in his class. If the student is an undergraduate or an unclassified student, s/he will receive a letter grade based on the table I3:J13. If the student is a graduate student, s/he will receive a letter grade based on the table L3:M12.

The threshold is the minimum percent grade required to attain the corresponding letter grade.

Complete the following formula in cell D2. The formula must work properly when dragged down to cell D15, and it must not be changed manually.

 

[Fluff]

No, this post post will not be deleted. Members are allowed to help if they so wish.
However they may point you in the right direction, rather than supplying the actual answer.