You'll want an LCD with composite or S-Video input. Otherwise you'd need a tv tuner, and lag would make any fast-paced game unplayable.
How it looks depends on the resolution of the LCD as compared to the output of the game console.
For sure, Dell 17" and 20" monitors come with the required inputs. I haven't tried it, but I think it's been done before. If no one chimes in later, I may try hooking my N64 up to my Dell 2005FPW.