A lens that is good for photographic purposes is often not good for scanning purposes. This is because while photographic lenses are made to focus on 3D objects at distances from about 50cm, we are trying to focus on a 2D object at much closer distances. Most photographic lenses will not even focus close enough to fill the digital frame with the film and you would have to crop. Even if it does, or you use extension tubes, this is not enough.
An optimal lens for scanning projects a flat focus field, has high resolution at close focus distances and has high contrast. These are complex concepts, but by looking through the price ranges that you are interested in, you should get a good overview of what will work for you.
Normal photographic lenses are not very suited for scanning
Stay away from vintage Zoom (variable focal length) 'Macro' lenses - they are not true macro lenses
Using extension tubes on a normal photographic lens will not give very high quality scans
Some macro lenses, vintage and modern, are excellent
You will find a lot of misinformation about lenses on the Internet and on forums
Note: That the categories you will find below are are ordered in order of price range, not in quality - generally the three last categories can provide similarly good results.
Should I use an autofocus or manual focus lens?
Using a manual focus lens will typically get you the same results at a much lower price compared to more modern auto-focus lenses. However, it might slow you down because you will want to check your focus between every time you set up or when you change the format, or something else about your scanning setup. Therefore, an autofocus lens can save time, particularly if you scan several formats.