LOOKUP, data rearranging without pivot tables?

[Hambone70]

I have two workbooks, an annual deconflictor, and a daily deconflictor (files attached).

Data will be manually inputted into the annual deconflictor, with the information being automatically linked to the daily deconflictor (as shown in the sample daily deconflictor file). Cell A1 in the daily file extracts the name of each daily worksheet. The inputted data is the reason for absence, but the linked data needs to link the person's name automatically into the appropriate absence reason column.

Can I do this without pivot tables? I tried various combinations of VLOOKUP, HLOOKUP, MATCH, and INDEX, but to no avail.

Thanks in anticipation!

[Hambone70]

Perhaps I need to explain better...