Macro always used to refer to close-up photography giving a reproduction ratio of 1:1 or greater (equivalent to a lifesize image on the negative).
Nowadays though it is used for almost any lens with close focusing abilities.

On SLR zoom lenses it is usually applied to lenses which give a reproduction ratio of 1:4 or better (i.e. 1/4 lifesize image on the negative).

True macro lenses are available for most SLRs, usually in 50mm or 90mm focal lengths, generally giving 1:2 or 1:1 reproduction. If you do not do much close-up work then a set of Extension Tubes or a Close-up filter are a cheaper option.

With Compact cameras 'Macro' is used to describe almost any camera capable of focusing closer than approx. 1 metre.