if statements to look at different worksheets and cells

I have a document with a large amount of data, and various formulas from countif to =if etc. However, In one particular worksheet cell b2 gets information directly from another cell in another sheet using the simple (='sheet2'!b2) formula. Now i want the cell underneath to read whats in b2, and dependant upon what it says (either Corrective Actions, Guidance or No Action) for it to look into a final sheet at a cell i want to determine, so if b2 says Corrective action, i want to look in sheet4!B4, if it says Guidance i want to look at sheet4!C4, and of it says No action, to look at Sheet4!D4 I have tried numerous times, but get the error #NAME? please help