Excel formula for autofill information

[blitzz008]

Hello Experts,

I am attaching an excel file with what I would like to do and with what I think is 'half' of the formula.

Basically I have certain agents that manage cases. I get the information of the cases each of the agents have and paste it in a tab on my file. Once this happens I would like to automatically fill in some columns and rows for each agent.
For example in the RAW tab is where I paste the information, I would like excel to search for the agent in the D column and if it matches the agent in the tab, to search for the matching row in column A and then paste the case number in Tab John Column B Row 8, and so on.

I hope this is clear, if not please let me know.

Thanks a bunch !