Google Sheets Complex IF Statement Searching for Matching Value

I am currently working on a spreadsheet and I am trying to create a mastersheet that pulls information from multiple fields from multiple different sheets and I am trying to make it as automated as possible.

I have run into a wall as this is getting more complex and sheets are not listed in the same format. I believe I will have to construct an complex if statement that searches the matching value then to pull the information required.

For example:

if cell value P281 matches another cell value in another sheet then take information from cell two places to the right from its position.

Could anyone point me in the easiest direction to accomplish what I desire?