I enjoy the dueling Excel podcasts between Bill Jelen and Mike Girvin! The challenges, the energy, the solutions, the community involvement…everything! Let’s look at Excel duel 141 (the PI episode). The best solution depends on asking one question.

Mike Girvin (ExcelisFun) solved it using three LOOKUP functions! It works as data is sorted.

=LOOKUP(B8,$K$13:$L$15)*LOOKUP(C8,$K$18:$L$23)*LOOKUP(D8,$K$26:$L$28)

dynamic solutions

If there could be let’s say 50 categories then we know we can’t fit 50 vlookups into a cell.

Put all the data together! Simply add more categories below. If you’re not afraid of tables then select anywhere inside of the data, hold ‘Ctrl’ key and press ‘T’ (to create a table). If you use a table then you don’t have to adjust your formula ranges.

Array Solution by XLarium

One array formula solves it!

To enter an array hold ‘Ctrl’ and ‘Shift’ keys and then press ‘Enter’ key (instead of just pressing ‘Enter’).

=PRODUCT(IF($R$13:$R$24=$B$7:$D$7,IF($S$13:$S$24=B8:D8,$T$13:$T$24)))

This array was created by “Xlarium“. He is awesome!

How does the array work?

Inside the PRODUCT function the goal is to extract 3 ‘Mult Factor’ numbers (as we have 3 categories).

IF($R$13:$R$24=$B$7:$D$7 normally an array condition is a single value. Here, we have $B$7:$D$7 (all 3 categories).

IF($S$13:$S$24=B8:D8 the 2nd condition filters to the goals that Andy achieved (0, 5 and 2).

$T$13:$T$24 qualifying answers (o.5, 2, 2) are taken from ‘Mult Factor‘ column and multiplied because the array is wrapped with the PRODUCT function.