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.

 

[denzo36]

can you attach the spreadsheet please

 

[fahadferoz01]

Many thanks. here you go. i am new and really do not know how to *** excel sheet here.

 

[denzo36]

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

 

[fahadferoz01]

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]

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))

 

[alansidman]

crossposted: Need assistance with Vlook up Formula.

crossposted: https://chandoo.org/forum/threads/pl...ttached.44308/

 

[fahadferoz01]

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

 

[Fluff]

@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]

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.