TL;DR answer: A video card upgrade is usually limited to the amount of power available from the PSU after subtracting your system's current power consumption.
I won't give a specific shopping recommendation here, as that's likely to be less useful than the general advice I'm giving here. In the following text, I am assuming that you cannot or are not willing to upgrade the power supply. Of course, if you can upgrade your PSU, some of the limitations discussed henceforth may not apply to you.
Assessing power usage and power available for upgrades
Finding a video card that will fit your system without further changes depends primarily on the power supply and the amount of power your system presently uses. The most advanced video card you can use is generally the one that can operate with the remaining amount of power available from the power supply. Look at the total power in watts that your power supply can supply as well as the number of amps available on the +12V rail, then assess the power consumption of the parts on your computer. This approach requires some research, using Google or otherwise, on the parts on your computer and the part you are about to buy. Of course, you can use an online PSU calculator such as the eXtreme Power Supply Calculator as well. Once you have determined how much power your system is using, you can subtract that from the total amount from your PSU to determine how much power you have available to upgrade.
Selecting a video card
There are many video card choices from NVIDIA and AMD today. Ultimately, your choice is usually limited by the amount of power available in the power supply. If, for example, you have 100 W left on your power supply, the highest power consumption (sometimes listed as TDP) video card you can choose is about 75 W (make sure you leave a small amount of headroom). This allows a lower midrange card such as a GeForce GT 440 to be installed, which may not be adequate to get a satisfying experience from your game (though still playable at lower settings or resolution). If you have 125 W or more available power, you have more powerful options that will handle modern games better, but make sure that there are one or more 6- or 8-pin power connectors on your power supply (the type and number required is listed in the system requirements of the card) and, in the case of high-end or top-of-the-line cards, that there is sufficient physical space to insert the card, as these cards tend to be very long.
Linked video cards
If you have multiple PCI Express x16 slots and sufficient power available, you can consider linking two or more video cards of the same type together. NVIDIA calls this SLI, while AMD refers to this as CrossFireX. This can bring significant increases in performance, often close to 90% greater than one card alone. However, this presents further challenges in cooling and power (remember that unless you upgrade it, you will be limited to whatever power is left in the PSU). Moreover, under certain circumstances, such as similar video cards running at different speeds, a multi-GPU setup may suffer from a problem known as micro stuttering, where the instantaneous frame rate intermittently becomes much lower than the average rate, causing stuttering that makes the video output appear worse than one card alone.
This may be an option if you have a large amount of power left in the power supply and need more performance than one card can provide alone. Of note is that all but the most basic of AMD Radeon 6000 Series graphics cards offer CrossFireX as an option (NVIDIA has very few SLI options at the entry level). If your system allows it, you can consider linking two entry-level Radeon cards to get more performance than one card alone while consuming less power than a high-end card.
Make sure your system is sufficiently ventilated and properly cooled, especially if the card is a high-end model, and that other relevant system requirements such as memory and processor speed are satisfied. You also want to make sure that if you want to make further upgrades such as a new hard drive, that there is enough power left in the power supply for these upgrades after installing a new video card. If power consumption, noise, heat, or cost are problems for you, then a high-end video card may not be the best choice, depending on the specific card, but you should find the best compromise for your performance and other requirements.
The +12V rail(s) on your power supply is also a factor to consider, especially if the power supply is a relatively cheap model or one that has multiple such rails (read on for info on multiple +12V rails). To determine the amount of power in watts on the +12V rail(s), multiply the number of amps on the rail(s) by 12, so 25A on the +12V rail means that 300W is available for high-power components such as the processor and video card. Almost all of the power from these components comes from the +12V rail(s). Some high-end power supplies have multiple +12V rails, which must be load-balanced so that the limits of each rail are not exceeded.