I am trying to write a single "IF" statement that uses the following two
conditions:

AND(VLOOKUP(LEFT($C2,4),Lookup!$A$3:$C$465,3,FALSE )="YES",$D2<100)

or

AND(VLOOKUP(LEFT($C2,4),Lookup!$A$3:$C$465,3,FALSE )="",$D2<25)

if either of these 2 conditions is met, place "Immediate" in cell N2,
otherwise leave it blank.

I seem to be missing a parentheses or something. :-(