Forgive me if I'm misunderstanding what you are trying to do - but I think you
may be overcomplicating things.

I would stick to just three columns: name position  and year. You can then filter
either by name or position, sort by year  to get jobs into order. I realise that
you will be duplicating the name field, but the spreadsheet will be much easier
to deal with than one that covers 200 columns!

I believe your situation requires a
database. I prefer using Access for such a task, and then using Excel as
a reporting tool.

Within Access; I would build an Employee table, then the data table for
all the positions and other information. Use ID fields for each table,
and link the employee ID to each position  held. You can build user forms
to enter the data, and export the information  via VBA to Excel.

The reason I suggest a database tool, such as Access, is once you create
your recordsets, all the queries you require, as per your message, are
easy to create.