Skip to content

Email Newsletter Challenge 8

This week’s challenge will test your ability to perform a two-way lookup. Which method will you choose? Let’s find out!

You can download the spreadsheet challenge using the links below.

The Challenge

Your challenge this week is to write a formula to lookup the quiz score for the selected student and quiz.

The Solution

The solution for this challenge is the same for both Excel and Google Sheets.

Select cell K7, and enter the following formula.

=INDEX(C5:H14, XMATCH(K4,B5:B14), XMATCH(K5,C4:H4))

The first XMATCH function looks up the position of the selected student among the listed students.

=INDEX(C5:H14, 5, XMATCH(K5,C4:H4))

The second XMATCH function looks up the position of the selected quiz among the listed quizzes.

=INDEX(C5:H14, 5, 3)

Finally, the INDEX function references all of the quiz scores (C5:H14), and returns the score in row 5, column 3.

NOTE FOR EXCEL USERS

If you are using an older version of Excel and do not have access to the XMATCH function, use this variation of the formula instead. (See the Solution 2 worksheet.)

=INDEX(C5:H14, MATCH(K4,B5:B14,0), MATCH(K5,C4:H4,0))

I hope you enjoyed this week’s challenge. And if you used a different method than I did, be sure to let me know in the comments below.

And if you’re not signed up for my weekly email newsletter, you can sign up right here!

This Post Has 2 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