I was introduced to tumeric when I began exploring Indian vegetarian cooking about 5 years ago. I buy powdered tumeric (along with other spices) at a store that caters to an Indian clientele.
I use tumeric primarily as a colorant and secondarily as a seasoning. As a colorant, it is used with grains, beans/legumes and vegetables in many cuisines. Spice blends and spice rubs may use tumeric in greater quantities, in which case it is also a seasoning.
Be aware that tumeric *stains EVERYTHING* - your counter, your towels, your apron - even your hands. I have never been able to get it out of fabric. I think I read somewhere that it is also used as a dye.
Tumeric can definitely enhance your cooking, but I think that, in general, the amount used is so small that it wouldn't have measureable health benefits just by itself (just my opinion
) but it is definitely worth exploring.