Skip to content

GS Challenge 3 – Lookup Functions

Welcome to your 3rd Google Sheets challenge article.

In this article, I will give you four different questions within Google Sheets to test your spreadsheet knowledge about lookup functions.

How many tasks will you be able to perform correctly? Click the link below to download the practice file to test yourself and find out!

Google Sheets Challenge File 3

Question 1

For question 1, you must write a formula to return the phone number of the selected employee.

Question 1 – Solution

There are actually several solutions to question 1. You can use the VLOOKUP function, a combination of the INDEX and MATCH functions, or the XLOOKUP function.

In this case, I will use the VLOOKUP function because the list of lookup values is in the first column of the data range.

So selecting cell F5, enter the following formula.

=VLOOKUP(F3, B4:C13, 2, FALSE)

The first argument is the lookup value which is the selected employee. The second argument is the entire data range.

The third argument specifies the column where the return value will come from. In this case, you want back the phone numbers, which are in the second column. So you enter a two for the third argument.

Finally, you make the last argument FALSE to force the VLOOKUP function to search for an exact match. As a result, you get back the phone number for the selected employee.

Question 2

This time, your goal is to get back the employee based on a selected phone number. You must craft a single formula to accomplish this task.

Question 2 – Solution

To solve question 2, it is probably easiest to use the XLOOKUP function because the lookup values are to the right of the return values.

So in cell F5, enter the following formula.

=XLOOKUP(F3, C4:C13, B4:B13)

The first argument of the XLOOKUP function is the lookup value. The second argument of the XLOOKUP function is the range of possible lookup values. And the third argument of the XLOOKUP function is the range of possible return values.

Once you have your formula entered, you get back the employee for the selected phone number.

Question 3

For question 3, your task is to calculate the letter grade based on the provided exam score.

Question 3 – Solution

Believe it or not, the LOOKUP function provides the easiest solution to question 3.

Simply select cell F5, and enter the following formula.

=LOOKUP(F3, B4:C13)

The first argument of the LOOKUP function is the lookup value, which is the exam score. The second argument of the LOOKUP function is the entire data range.

The LOOKUP function will search through the first column to find the closest value that is less than or equal to the lookup value. The function will then return the corresponding value from the last column of the selected range, which gives you back the letter grade.

It is important to note that the lookup values must be sorted in ascending order for the LOOKUP function to work properly.

Once you have the formula written, I encourage you to experiment with changing the exam score to better understand how the LOOKUP function works.

Question 4

For your final question, you must write a formula that returns the exam score depending on the selected student and the selected exam.

Question 4 – Solution

To pull off question 4, I suggest using a combination of the INDEX and MATCH functions.

So in cell H6, enter the following formula.

=INDEX(C4:E13, MATCH(H3, B4:B13, 0), MATCH(H4, C3:E3, 0))

The first argument of the INDEX function references the entire data range of exam scores.

The second argument employs the MATCH function. The MATCH function searches for the selected student (first argument) within the list of students (second argument) and looks for an exact match due to the zero as the third argument.

The MATCH function then returns the relative position of that student. This number becomes the second argument for the INDEX function.

=INDEX(C4:E13, 5, MATCH(H4, C3:E3, 0))

The third argument of the INDEX function also uses the MATCH function.

The MATCH function searches for the selected exam (first argument) within the list of exams (second argument) and looks for an exact match due to the zero as the third argument.

The MATCH function then returns the relative position of that exam, and this number becomes the third argument for the INDEX function.

=INDEX(C4:E13, 5, 2)

Finally, the INDEX function returns that exam grade at the intersection of the second and third arguments.

And that officially completes your Google Sheets Challenge number 3!

How Did You Do?

I hope you had a blast working through this Google Sheets Challenge! Let me know in the comments how you did. I’d love to hear your thoughts.

Also, if you missed the first two challenges, you can find them below.

Be sure to keep an eye out for the next Google Sheets challenge in the very near future.

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