The first look-up table effect that really impressed me was to use them for making a textured tunnel.
You have a look-up table such that for each pixel on the screen, you know its angle and distance from the center of the screen. With this you can pick which texel to put at each pixel location.
At first I thought the game Stardust must have used this effect, but reading up on it just now they actually just play a repeating 6 frame animation for the background! https://codetapper.com/amiga/sprite-tricks/stardust/
He gave one of my favourite GDC talks of all time [1]! I recommend this talk to anyone who is interested in the details and history of working on limited colour palette games!
I remember games that used 8-bit color palette cycling to do tear-free renders in an interesting way.
They would draw the next frame of the game direct to the screen in either the high nybble (4 bits) or the low nybble (alternating each frame).
The color palette was either one of two: one where the same color would be displayed regardless of the high nybble and the other palette had colors chosen where they were the same regardless of the bits in the low nybble.
To be sure the game threw away 256 possible colors to have instead only 16. But when the next frame was being rendered, the user was never aware until, at the very last instant, the next palette was slammed in revealing the new frame (and of course the rendering began for the next).
Yes, you could for example achieve the tunnel effect described above with a 16x16 or 32x8 texture using a 256-entry palette.
Unreal by Future Crew has an effect that comes to mind that is probably implemented exactly like that (on VGA mode 0x13), just more of a wormhole than a tunnel: https://www.youtube.com/watch?v=InrGJ7C9B3s&t=4m40s
You have a look-up table such that for each pixel on the screen, you know its angle and distance from the center of the screen. With this you can pick which texel to put at each pixel location.
It looks as if you are moving in a tunnel with 3D geometry, but it's so cheap you can even do it on pico: https://www.lexaloffle.com/bbs/?pid=63818
At first I thought the game Stardust must have used this effect, but reading up on it just now they actually just play a repeating 6 frame animation for the background! https://codetapper.com/amiga/sprite-tricks/stardust/