API/BUG: make .at raise same exceptions as .loc
- [x] closes #31722
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry

This also (very) indirectly addresses #31683 which in turn will let us get rid of `CategoricalIndex.get_value` altogether.