I do plenty of Macro and product photography (love the Tamron 60mm Macro). I have no idea what videographers who use DSLR like for lenses. Any advice and broadest usage appreciated. Assume camera in the 15 MPixel range plus or minus a few MPixels.
Properties that are desired in these lenses are quiet operation, image stabilization, zoom construction that usually cover wide to short tele focal lenght and that have a constant aperture across that range. A perfocal design is good when using a zoom. These are a couple of general characteristics but they may be different for special applications.
Quiet operation helps if you want to avoid focusing sounds appearing in your video. If you're using external microphones this is less of a problem.
Image stabilization makes everything (from static vieography to panning= appear a lot smoother.
To have a zoom lens gives a lot of nice possibilities and constant aperture is an important feature since the exposure won't change when zooming. A parfocal lens is a lens that maintains its focus when changing focal lenght (zooming) and is of course very useful. Also a zoom range from wide to short tele is usually enough and you probably don't want to go out of that range during video production unless you're looking to produce nature films and alike.
Look for for lenses with this kind of properties. Being a Canon user I'm most familiar with them and can recommend Canon EF 24-105/4,0 L IS USM having most of these features but if you're using another brand I can't help ypu more than this due to lack of experience with other brands.