Yes, it puts all three programs into 1 buddy-list/application. Its just the same as any other messenger, but with your buddies from all three messengers onto 1.
I use Trillian so I don't have to open MSN and Yahoo messenger at the same time. It's pretty cool and nifty, saves a lot of space 'cause all the contacts are in just one window. Trillian is good for chatting, but I don't think you can use the advanced options available in the original chat programs (like the environments in YM for example). Also, my friend using YM tried sending me a file but it didn't work I only tried it once and I haven't explored Trillian much yet so maybe there's a fix to this or something...
Trillian is great. But remember, if ICQ changes its message-protocol, you have to update trillian or you cannot use ICQ-function. This happens sometimes in the year!