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

Dear Jeff.

I have two methods for this problem. The first one is the match-index which you can see formula below:

=INDEX($C$20:$E$23,MATCH($B11,$B$20:$B$23,0),MATCH($C11,$C$19:$E$19,0))

The second one is sumproduct function. The formula is described below:

=SUMPRODUCT((($B$20:$B$23)=$B11)*(($C$19:$E$19)=$C11)*($C$20:$E$23))

I would like to know what’s your opinions about these methods.

i look forward to hearing from you.

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:

=VLOOKUP(B11,$B$20:$E$23,MATCH(C11,$B$19:$E$19,0),0)

Looking forward to your next challenge.

Bob

Outstanding approach … I love it!

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!

Thanks!

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

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

=VLOOKUP(B11,$B$20:$E$23,MATCH(C11,$C$19:$E$19,0)+1,0)

i used the vlookup but added an additional column.

Scott … nice approach!!