I have a 2005 Pilot. By 2006 the transmission was well sorted, except the external sensors do wear out. They are cheap and easy to replace yourself.
Look for:
Blinking "D" light. May have to take a long drive to get it. If no CEL, use it as a bargaining chip (see above). If goes with CEL, avoid buying, truly bad transmission.
Pink milkshake in coolant, purple ATF. Means it needs a new radiator yesterday.
Rocking driver's seat. The metal seat frame cracks. The fix is either a $600 assembly, or fabricate a plate, remove 2 bolts and grind away 2 rivets and put plate in place.
Burnt out resistor or control disables rear HVAC. A couple of simple/cheap fixes here, use as bargaining chip.
Check front HVAC mix and blend operation. Both of these motors tend to go out around 150K, pick up rebuilds on eBay for $90. I rebuilt mine myself using pick and pull parts, kept parts swapping internals until I got a good mix. Clicking noise is your clue.
What zfast03 said, in maintenance history you also need to look for VTM-4 (rear diff) fluid changes.
If the engine burns oil, it may just need a new PCV valve, which is affordable and easy to replace, but replacement was not part of the original maintenance schedule (other than "check operation").
Given my 211K mile Pilot experience, I would jump on a good deal on a 2006 MDX or Pilot.