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

fahadferoz01

New Member
Joined
May 17, 2020
Messages
21
Office Version
2016
Platform
Windows
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.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
84
Office Version
365
Platform
Windows
can you attach the spreadsheet please
 

fahadferoz01

New Member
Joined
May 17, 2020
Messages
21
Office Version
2016
Platform
Windows
Many thanks. here you go. i am new and really do not know how to *** excel sheet here.
 

Attachments

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
84
Office Version
365
Platform
Windows
So to populate the status column you'll need to left function with vlookup and use approximate match. If you set up the Status table like below having your thresholds as 1, 3, 6, approximate match will return UNCL for 1-2, UGRD for 3-6 and GRAD for 6-9. You need to use the left function to get the first digit of the student no and mulitply then answer by 1 as the left function will convert your number to text and you'll need to convert back to a number for the data types to match in your vlookup

1589705412208.png
 

fahadferoz01

New Member
Joined
May 17, 2020
Messages
21
Office Version
2016
Platform
Windows
Sir, i
So to populate the status column you'll need to left function with vlookup and use approximate match. If you set up the Status table like below having your thresholds as 1, 3, 6, approximate match will return UNCL for 1-2, UGRD for 3-6 and GRAD for 6-9. You need to use the left function to get the first digit of the student no and mulitply then answer by 1 as the left function will convert your number to text and you'll need to convert back to a number for the data types to match in your vlookup

View attachment 14053
Hi Sir, many thanks for your help. I am not beginning to understand how the vlook works. is there any way i can send you the excel sheet. perhaps your email id so that i can send and you can have a better look on this.
 

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
84
Office Version
365
Platform
Windows
for the grade assignment you could you the if, or and vlookup with approximate match to get the grades

so cell D2 formula =if(or(B2="UNCL",B2="UGRD"),vlookup(C2,$I$3:$J$13,2,1),vlookup(C2,$L$3:$M$12,2,1))
 

fahadferoz01

New Member
Joined
May 17, 2020
Messages
21
Office Version
2016
Platform
Windows
Sir, i

Hi Sir, many thanks for your help. I am not beginning to understand how the vlook works. is there any way i can send you the excel sheet. perhaps your email id so that i can send and you can have a better look on this.
HI SIR, I FOLLOWED YOUR STEPS AND COMPLETED IT. BUT THE EXERCISE WANTS THE FORMULA TO BE LIKE THE ATTACHED PAGE. HOW TO DO THAT? PLEASE HELP ME
 

Attachments

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
@fahadferoz01
Please take the time to read the rules.
This is your first day here & you have cross posted without providing links and also duplicated you posts.

As this is obviously a homewrok question, we ask you to mention that & show what you have tried.
 

fahadferoz01

New Member
Joined
May 17, 2020
Messages
21
Office Version
2016
Platform
Windows
sorry for it. now will this post be deleted? or what can i do? all the forum people are not that helpful. Plus - Is home work questions not allowed to post here ? and the images i gave is to show that i tried. Dont see any harm on that.
@fahadferoz01
Please take the time to read the rules.
This is your first day here & you have cross posted without providing links and also duplicated you posts.

As this is obviously a homewrok question, we ask you to mention that & show what you have tried.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,162
Messages
5,467,000
Members
406,516
Latest member
richcresswell

This Week's Hot Topics

Top