C.C.
We've had the Nova Foam from Costco for about 6 months, and bought it upon the recommendation of a friend who had had his for about a year. LOVE IT. If you read up on it, it's the same technology that TempurPedic has. Suffice it to say that when we got rid of our old pillow-top mattress, I was practically crippled. My neck and back hurt all the time. Now, after 6 months on a memory foam mattress, my back and neck feel great! (I also bought a Nova Foam pillow at Costco - love that, too!)
The only thing to be aware of is that when you first take it out of the box, it will smell funny. However, the smell dissipates within a few days.
Also, one thing I had read in some of the older reviews was that memory foam mattresses make you hotter, but my husband is one of those people who always feels hot, and he has not noticed any difference in between the memory foam and the old pillow top in terms of sleeping temperature. I know this is something memory foam manufacturers have been working on, and apparently they've made improvements in the past few years.
For the money, you can't beat the Nova Foam mattress, in my opinion.
ETA: My daughters both have memory foam mattresses from IKEA, and those have worked out pretty well also. The only thing is, they are both young and so they don't weigh much - therefore I'm not sure how the mattresses hold up under normal adult weight over time. Guests we've had stay over have said the mattresses are comfortable though. So, IKEA may be another option to check out.