Conditional Formatting - 2 sheets using Vlookup or Index/Match or SumProduct?

I'm working with Excel 2010. I have two sheets. I have a value
in column D on Sheet1. I need to look up that value in Sheet2, column
A. If that value is there on Sheet2, I need to look at the offset
value in that row that is in column C (still on Sheet2). IF that
value is 'Need', then I need the color of the cell on Sheet1 to change
to Red. I hope that's clear enough.

So, basically it would be a VLookup with an Offset after it finds the
value. Here are a couple things that I've already tried that haven't worked:

I first tried this:

=Vlookup($D2,NamedRange,4,False)="Need"
(where D2 is on Sheet1, 'NamedRange' is on Sheet2 and is 4 columns
wide and 'Need' may be in the 4th column)

I also tried this:

=INDEX(Sheet1!$D:$D, MATCH($D$2,Sheet2!$A:$A))="NEED"
(although, I realize the Match doesn't include Column D where the
'Need' value would be)

And I tried this too:

=VLOOKUP(D2,EventDescription,MATCH("Need",$D$D,0),FALSE)

I believe there is some combination of Vlookup, or Index/Match or maybe even SumProduct that
will do this, but I've just not been able to make the combination. I'd
appreciate any help with this. Thanks!

Your first formula should work.

When you put that same formula in a cell, do you see the TRUE/FALSE result? Once you get your formula doing that in a cell, you can transfer it into a CF rule.

Yes, it should be OK. I mocked it up for you and used ths:

=VLOOKUP($D$2,Sheet2!$A$1:$C$20,3,FALSE)="Need"

works fine...

Hang on though... In one place you talk about "Need" being in column C and in another about it being in the fourth column (i.e. D). Which is it??