Index formula #Num! error and cell displays wrong result

Hi,
I'm using the index formula

=INDEX(Vorlesungen[[Name]:[Name]];SMALL(IF(Überblick!A$1=Vorlesungen[[Semester]:[Semester]];ROW(Vorlesungen[[Semester]:[Semester]])-1);ROW(1:1)))

to look up a name in my data sheet (Vorlesungen) and my formula builder always shows the correct result, however the cell does not.

It would be great if someone could check out my document and see where I went wrong. Check out the formula in Überblick!A3. It shows "Analysis 1" but the formula builder for the INDEX function shows "Latein" (the correct course). For all the cells below (e.g. Überblick!A4) it shows #NUM! although once again the formula builder shows the correct result (Differentialgleichungen).

Thanks!

It's an array formula that needs to be entered with CTRL + SHIFT + ENTER

Formula is 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.

Thanks! You're a life saver!

You're welcome.