Index and Match... I'm pretty sure I'm just doing it wrong

Hi.

I've been trying to figure out a way to save time with some monthly reports... What I have been doing is taking my raw data (separate file), setting up a pivot table, breaking out those pivots, and then doing vlookup up against each of the new sheets in each column... let me tell you, I'm sure there is a better/faster way... and I think it has something to do with Index/Match.

I won't bore you with all of the variations that I've tried. Here's the short version. All I want to be able to do is see who has "Completed" each training.

I've attached my workbook. What I'm trying to do is figure out (find) the formula that will compare the Username and ID to the Training and Item Name , and if they are both there... mark the Item Status in C2:I21 on the Completed Skills sheet.



Any help is much appreciated.
Oh, and if you need more info, please let me know.
Thanks.

That is not quite a straightforward INDEX/MATCH, although you were correct in using it. Try this ARRAY formula, copied down and across...

=INDEX(RAW!$D$2:$D$123,MATCH('Completed Skills'!$A2&'Completed Skills'!C$1,RAW!$A$2:$A$123&RAW!$C$2:$C$123,0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.