Firstly, how much are you willing to spend? That is a huge determining factor in what you can purchase and what features we can forgo for the essentials.
That being said, I would seriously consider either getting a DSLR if you really want to take good macro shots. It can be a bit expensive, but depending on why you want to do this, may be worth it. You can get some smaller flexible tripod stands that will hold DSLR cameras no problem. The GorillaPod SLR-Zoom and Focus models would work if you did that.
There are some P&S cameras that would work alright for what you're trying to do, sure, but keep in mind a few drawbacks to using one over a DSLR:
1) The viewfinder. Most small cameras have tiny or no viewfinders, so you would have to use the LCD on the back to know what you are shooting. From personal experience, even using the LCD on a DSLR makes it hard to know how well the picture is going to turn out. Some things look really good zoomed out, but when you bring it to regular size, you see the DOF and general focusing is all messed up, and it's too late to take another shot in some cases. The DSLR viewfinder is pretty essential for a decently focused shot. Course, on the flip side, if you are using a small tripod you may find it difficult to use the viewfinder anyway, so keep that in mind. Most macro photography is done by actually holding the camera steady while taking the shot, and most of the time with the help of an LED ring light to make motion blur happen less since the shutterspeed will be nice and fast. Which brings me to my next point:
2) The flash. Point and shoot flashes are mostly terrible for macro shots. Most are badly positioned for those types of shots, so a hot-shoe for a mountable LED ring light becomes almost necessary. Some P&S cameras have these, but they are mostly on "bridge" type cameras and all DSLRs. However, you can circumvent this indoors by using a LED lamp and positioning it so you flood the subject in light if it's inanimate.
3) The sensor. P&S cameras always have a smaller sensor that bridge and DSLR cameras. Due to this, there will be more noise in the shots overall which is very noticeable on macro shots. The only way to get around this (at least somewhat) would be to use a lower ISO in a well lit area. Without an LED light/flash of some sort, you're gonna have problems, ESPECIALLY on a P&S.
You can always try focus stacking, but I personally don't like it. With your tripod, you could probably achieve the stability to make focus stacking work, but meh.
So after that list of drawbacks, if you still want to use a P&S, then give me a budget and I'll see what I can find for you. Don't expect a camera fitting your needs to be super cheap, I'd probably say the absolute cheapest for something "macro-ish" would be $200, most are probably over $300.
