Gamification 3: Challenges

This is the third post in the Gamification + Excel Training series, and in this post we talk about challenges. One component of our gamification strategy is to encourage competition by hosting challenges. In general, competition is motivating and can help people push themselves to accomplish more. So, it is an element used in our Campus Pass, where our goal is to provide a fun and motivating place to learn Excel.

As you can image, there is almost no limit to the types of Excel competitions we can design. But, in this post, we’ll provide an example of a formula challenge.

The idea is straightforward. You have to write a formula that provides the correct result. Given the vast number of functions available in Excel, there are many ways to write a formula that will provide the correct result. The beauty of formula challenges is that students can see other approaches to the same problem that they may not have considered. The student can then make a mental note of it in case they encounter a real-life workbook where it would be helpful.

For example, let’s say you are preparing an invoice in Excel, which looks a bit like this:

The Prices come from a pricing matrix that looks like this:

Challenge: Write a formula that retrieves the correct Price for each invoice line from the price matrix. You must be able to fill the formula down and it should work for all invoice rows.

If you’d like to give it a try, download the file below:

If you’d like to see one possible solution formula, check out the video below:

If you decide to participate, please feel free to copy your formula and paste it as a comment below…thanks!



Get a quick email notice when a new Excel article is available

  • This field is for validation purposes and should be left unchanged.

This article was written by Jeff Lenning


  1. shakoor

    Dear Jeff.
    I have two methods for this problem. The first one is the match-index which you can see formula below:
    The second one is sumproduct function. The formula is described below:

    I would like to know what’s your opinions about these methods.
    i look forward to hearing from you.

    1. Jeff Lenning Post author

      Well done Shakoor! I used the INDEX/MATCH/MATCH option as well in the solution video 🙂


    Hi Jeff,
    Per your first requirement of writing “the shortest formula you can . . . “, I offer the following 2-way lookup formula located in cell D11:

    Looking forward to your next challenge.

    1. Jeff Lenning Post author

      Outstanding approach … I love it!

  3. Eric

    Completed! I started by making the price chart into a table and then did the index/match/match formula. Then I played around with adding a column to the right for X Large and a row at the bottom for Purple and it still worked!


    1. Jeff Lenning Post author

      The table idea is great, and nice to hear you tested it with additional rows/columns … awesome!!

  4. Peter Puggaard

    I did a query from the price table/area, Unpivot data, Add Coloumn: Shirt Options & Size. Then the formula looks like:

    =VLOOKUP(TEXTJOIN(” “;1;B11:C11);$J$2:$K$14;2;0)

    My query data table is in J2:K14

  5. Scott Huval


    i used the vlookup but added an additional column.

    1. Jeff Lenning Post author

      Scott … nice approach!!

Leave a Reply

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

For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.