Use it for pages that must exist for users but have no business in results: internal search results, thank-you pages, filtered views, staging environments and printer versions. Combined with `follow`, the page still passes link value onwards, which is usually what you want for a filtered listing that links to real products.
The classic failure is combining noindex with a robots.txt disallow on the same URL. The crawler never fetches the page, never sees the directive, and the URL can stay indexed indefinitely. If a page is already indexed and must come out, allow the crawl until the removal is confirmed.