The denser (and harder) the surface of the material to be sanded, the higher grit of the abrasive can typically be used with it to still be able to achieve an increasingly finer surface.
For example, to achieve a mirror surface on a steel part (e.g. handmade fine craft knives etc.), and I mean actual mirror finish, not just glossy/shiny, meaning that it's properly flat or otherwise uniform, of a very low roughness and without any scratches visible to the naked eye at any angle, one would go progressively up in grit to at least P2000, maybe beyond, and only then use abrasive pastes (of course with a respective technique, not just rubbing it against a "polishing" wheel on a bench grinder, but that's another story).
Wood, of course, being soft and porous, will not typically benefit from higher grit polishing, unless maybe when it's real hard, like that exotic stuff that's illegal to export, and/or covered with a particular coating that can benefit from it.
Another example: when natural sharpening stones are prepared, they are first lapped (made flat), and then "conditioned" by rubbing them in a certain fashion on a flat surface (typically a sheet of glass, granite or cast iron) with some loose abrasive powder to set a specific roughness to their working surface. It is not uncommon to use SiC powder as fine as F1200 (same as ANSI 1500) on a final stage, which is roghly equivalent to about P6000-P7000 sandpaper grit (~3 micron particle size). Similar to sanding, it typically only makes sense to do this with hard types of stones, one of the most commonly known examples being translucent Arkansas.
p.s. yes there's a shitload of various grit standards. One could go insane just trying to convert one to another, and they often aren't directly comparable, too.