To me, Super Castlevania IV and Castlevania64/LoD nail the "Atmospheric", "Ambient" and "Situational" tunes.
In Super CV IV, most people think of the highlight pieces (Theme of Simon, Chandeliers, etc.).
However, think of the pieces in the game that have set the mood, not the ones you put in your MP3 player:
1. The title screen music with its deep acoustic base and low base woodwinds.
2. The Intro music (Theme of Dracula), with its low pianoforte notes (also used when the torches light up at the Castle Keep). It does pick up.
3. The skull rock before the drawbridge music. It's just low strings playing two notes, but it gets you somewhat fearful.
Castlevania64/LoD
1. The Castle Wall starts up with a little Bloody Tears jingle, but that's not the music throughout the stage. You get the slow-paced "Watchtower" music.
2. Once you make it over the wall, and get into the Villa's grounds, there's a part in the game in which the entire stage goes dark and the fire cerberus hounds show up. There's a jump in the music here that really sets you in a chilling, almost "Resident Evil" claustrophobic mood. It is later accented by the "Silent Annex" harpsichord piece. Although Silent Annex is a stage theme, when you get into the dining room and the rose falls on the floor, at the cutscene, you get another musical cue. These cues are not present in the PS2 titles.
3. There are times later in the game in which you bump into Malus and you begin to see that he's... 'off'... although they are quite obvious, they are exemplified by the music that goes along.
I think Lords of Shadow is going to have music more similar to the n64 titles, in that it matches both story, and area. That is to say, when we get to a point in the story in which we're supposed to be pumped up and ready to take on the challenge, then and only then will we hear Vampire Killer, etc. But since that's probably gonna be near the later third of the game, that's not gonna be shown in any trailer video.