API/ERR: allow iterators in df.set_index & improve errors
- [x] closes the parts of #22484 (resp. those worth keeping) that were reverted in #25085 due to #24969 ~closes #24969~
- [x] tests added / passed
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`

This is a quick fix for the regression - however, I think this should be immediately (i.e. 0.24.1) be deprecated. I haven't yet added a deprecation warning here, pending further discussion in the issue.

@jorisvandenbossche @TomAugspurger @jreback 
