Try lenses you already have first. Get a set of Kenko macro extension tubes to use with them, if they don't give enough magnification. Those tubes can be used with virtually any lens that fits your camera and can be handy for many things. The Kenko are good quality, well made with sturdy latching mechanism. They also have electronic contacts to allow autofocus and aperture control. Not cheap at around $125 for the set of three (12mm, 20mm & 36mm), but that's actually a good deal compared to OEM macro extension tubes from Canon and Nikon that can cost more than that for a single tube. There are cheaper third party sets (Opteka and Zeikos, both of which are frequently relabeled and sold under many other brand names). They cost between $50 and $75 for a set of three, but are more "plasticky" and don't have as sturdy and secure latch mechanisms. The cheapest ones have plastic bayonet mounts, too... which I wouldn't trust to use with heavier cameras and lenses.
Around 85/90mm to 100/135mm focal lengths are about the easiest to use and what I usually recommend. These give you adequate working distance while still being reasonably "hand-holdable". Shorter focal lengths make for less working distance, which might scare away live subjects or cause you to cast a shadow on your subject. Longer focal lengths are more likely to require tripod to get a steady shot (image stabilization doesn't have much effect at higher magnifications). Longer focal lengths also make for very shallow depth of field and stronger background blurs.
But virtually any lens might be used... For example:
20mm wide angle lens (non-macro, with a 12mm extension tube) used for added depth of field, so the flowers in the background would remain recognizable (note... a short focal length leaves very little working distance... in this case using the shortest extension I've got, the petals of the flower were actually touching the front element of the lens)...
An unusually close focusing (five foot) Tamron SP 500mm f/8 "mirror" lens (no extension tube needed, note the strong background blur... there's a wall, fence and pile of firewood a few feet behind the flower)...
I also sometimes use non-macro lenses for special effects they produce.... For example, I know my 50mm f/1.4 at larger aperture settings and forced to focus closer (here with 20mm extension tube) will produce a sort of soft focus effect, as well as some vignetting...
Or, just use macro tubes to be able to achieve higher magnification than normally possible, such as this which was done with an old, manual focus Tamron SP 90mm macro lens that can only do half life-size on its own, but a lot higher when an extension tube is added (20mm in this case)...
Note: That 1980s era Tamron 90mm only cost me $20 at my local second hand store, plus another $40 for a mount to be able to fit it to my modern Canon DSLRs (Tamron made a whole line of manual focus "Adaptall" lenses with interchangeable mounts, that can easily be adapted for use on almost any camera system... modern or vintage. The lens used for the above came with a Nikon mount, and I have mounts that allow me to use it on old Canon FD, Konica K/AR, Pentax P/K and more. It's manual focus and manual aperture only, so a little slower to work with. Manual focus is often easier for macro work anyway. But manual aperture means the lens actually stops down, dimming the optical viewfinder used by most DSLRs and making manual focusing more difficult.)
Don't hesitate to try a zoom, either. This was shot with a 25mm extension tube on 70-200mm lens (didn't have a macro lens with me... but I do ALWAYS have some macro extension tubes. Sorry, not a flower example this time)....
Canon has a newer 24-70mm f/4L IS USM lens that's able to shoot at an amazing 0.70X all on it's own... that's just slightly shy of 3/4 life size (very close to what most macro today do... 1:1, 1.0X or full life size). But even at around 1/4 life size, my older 24-70mm zoom focuses pretty darned close without any extension tube...
A true macro lens is fastest and easiest to use, though. My most frequently used is a modern Canon 100mm (which I especially like because it can be fitted with a tripod mounting ring... few other macro lenses shorter than 150mm or 180mm offer that option)...
Other macro lenses I use include a Canon MP-E 65mm, a rather specialized and slow to work with manual focus lens capable of ultra high magnifications (no less than 1:1 and up to 5:1). This newly hatched baby snail is only a few millimeters long and is at approx. 3X life size...
I also use a Canon 180mm macro lens to work from greater distances that some subjects such as the rather unhappy tarantula below might require... but notice in shot of the bee how ultra-shallow depth of field is rendered with such a long focal length at high magnification...
There are other ways to "do macro and close-up" photography. For example, "close up lenses" can be added to existing, non-macro lenses. The better quality multi-element "diopters" such as Canon 500D and 250D can do a pretty good job, but ain't cheap and since they screw into the front of your lens like a filter, aren't nearly as versatile as macro extension tubes. Reverse mounted lenses and reversed/stacked lenses are two more ways. Reverse mounts simply screw into the front of an existing lens to allow it to be mounted on the camera backwards, which will cause it to render higher magnifications. That's cheap to do, but the problem is that in most cases doing this with modern cameras and lenses means no autofocus and, even worse, no means of controlling the lens aperture. Reverse/stacked allow two lenses to be used together, with one turned around and mounted backwards on the front of another lens that's mounted on the camera. The reversed lens sort of acts like a diopter. The problem with this is that you need to get a pair of lenses that work well together, ideally with the same or close to the same filter thread size and usually something like a 100mm or 135mm mounted on the camera with a 24mm or 28mm reversed on the front of it. It sometimes takes trial and error to find a pair of lenses that "play well" together. Reverse/stacking does allow for some autofocus and aperture control, though.... so long as the primary lens mounted to the camera offers those.
I realize this is just scratching the surface of a very large topic, but hope it helps! There are a lot of books about the subject, with far more detail and expertise. John Shaw's "Close-ups in Nature" is sort of the "bible" for this type of photography, if you can find a copy of it. There are many other books by various authors: Joe and Mary MacDonald, Tim Fitzharris, Heather Angel, Nial Benvie.... to name a few that come to mind.