Its going to need a transmission sooner or later...
I asked one of my previous mechanics, who specializes in Hondas, about the older Pilots, Ridgelines, and Odyssey vans. He said that he wouldn't count on the transmission lasting long in any of them. He said the transmission in the cars back then were very reliable and durable, but not in the bigger Honda vehicles. Reason I asked the mechanic was because a friend was shopping for one, and it was a common concern back then. I don't have any personal experience with any of them though.
A modern car with AWD and bed would be handy for a lot of people though. Should ride nice, be quiet, etc. If the tranny craps out, then just rebuild it. I wouldn't worry too much about it. Just be prepared, as with any used vehicle.
The only other concern that I would have are reports of Pilots and Ridgelines getting stuck on steep grades. Not spinning wheels, but the transmission overheating or traction control freaking out. Not an issue on pavement, but only on really long steep gravel roads. I don't know the cause of the shutdown, but have heard a few people complain about it.
