Skip to content

Excel Challenge 5 – Lookup Functions

Welcome to your fifth Excel challenge! In this article, I will give you four questions to test your Excel skills when it comes to lookup functions.

How many questions can you get right? Download the practice file below to test yourself and find out!

Excel Challenge Number 5

Question 1

For your first question, your goal is to look up the grade Yoda received for exam number 2.

Question 1 – Solution

To solve question 1, you can use a variety of different functions. So there is more than one solution.

For my example, I will be using the HLOOKUP function. So selecting cell F10, enter the following formula.

=HLOOKUP(C10, C4:J7, 3, FALSE)

In this formula, the HLOOKUP function first references the lookup value of ‘Yoda’ in cell C10.

The HLOOKUP function then references the range C4:J7 and looks for the lookup value in the top row of this range.

Once the HLOOKUP function finds the value of ‘Yoda,’ it returns the value in the third row of that column due to the third argument.

It is also important to note that the FALSE value for the fourth argument forces the HLOOKUP function to search for an exact match.

As a result, you get back Yoda’s grade for exam number two.

Question 2

For question 2, your goal is to write a formula to calculate the letter grade for each student based on their final score.

Question 2 – Solution

To solve question 2, select cell D5, and enter the following formula.

=VLOOKUP(C5, $F$5:$G$9, 2)

The VLOOKUP function references the final score as the lookup value.

The function then looks for an approximate match of that value within the first column of the referenced range ($F$5:$G$9). Note that this is an absolute reference.

And by approximate match, I mean that the VLOOKUP function searches for the value that is closest to and less than or equal to the lookup value.

Once the VLOOKUP function finds a match, it returns the corresponding letter grade. And after you write the first formula, you can use the fill handle to copy the formula down for the remaining students.

Since the first argument is a relative reference, it will update for each student. And since the second argument is an absolute reference, it will stay the same for each student, which is what you want.

Question 3

For question 3, your goal is to calculate the quiz score for the selected student and quiz.

Question 3 – Solution

To solve question 3, it is probably best to use a combination of the INDEX and MATCH functions.

So in cell J7, enter the following formula.

=INDEX(C5:G12, MATCH(J4, B5:B12, 0), MATCH(J5, C4:G4, 0))

The first MATCH function searches for the value in J4 (the selected student) within the list of students and returns the position of that student.

The second MATCH function searches for the value in J5 (the selected quiz) within the list of quizzes and returns the position of that quiz.

The INDEX function then uses the two MATCH function results to pinpoint the location and return the desired quiz score.

Question 4

For question 4, your task is to write a formula to return the letter grade of the student whose name begins with the indicated letter.

Question 4 – Solution

For question 4, I recommend using the XLOOKUP function.

Select cell G6, and enter the following formula.

=XLOOKUP(G4&"*", B5:B8, D5:D8,, 2)

The XLOOKUP function references the text value in cell G4 and uses the asterisk as a wildcard character. In other words, the XLOOKUP function will search for a text value that starts with the text in cell G4. The wildcard character will then match any text that comes after the text in cell G4.

The XLOOKUP function searches for the lookup value within the range B5:B8 and returns the final value from the range D5:D8.

It is also very important to note that the fifth argument of the XLOOKUP function is set to two. The two allows the XLOOKUP function to use the wildcard character and search for an approximate match.

As a final result, you get back the letter grade for the student whose name begins with the specified letter in cell G4.

And that officially concludes Excel challenge number 5.

How Did It Go?

How did it go? How did you do?

Were you able to correctly answer all of the questions? Or were some a little more difficult than expected?

Leave me a comment below and let me know! I’d love to hear your thoughts.

Learn By Doing

If you enjoyed testing your knowledge like this and found that you learned a few things along the way, then I recommend that you check out my Excel Total course.

The Excel Total course is designed to teach you by allowing you to apply what you learn. That way, you get plenty of hands-on practice!

After all, one of the best ways to learn is by doing. And that’s what the Excel Total course is all about!

Watch the Video

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top