It looks as interesting as painful it would be to use in current state.
For example classic megaman spritesheet:
Roughly 92 sprites(roughly as you won't need it for separated vehicle and teleport, but OK, characters can have much more than that and less than that, for mugen character it would be somewhat around 500 for averaged character) without any shading. Now quadruple them as you need to draw shading 4 times for lighting profiles(and if you a complete masochist and accuracy freak 8 times), it 368 sprites(for freaks - 736). At this point you'll understand that switching colors for different armors is genial as there's no need to repeat the process. OK, you've been through hell drawing those, now what? You need to put that in the game somehow. For that you need to know where would be those moving light sources in your game to prerender needed frames. Let's see, lighting bolt can fly to the character's face, above and under him with different distances for it. Let's say we simplify excluding situations where light comes from background or foreground, let it be 3 gradations of light from 8 directions, that's 2208 sprites.
and there I already forgot situations where you'll have light from 2 or more sources at once, though OK, that feature wasn't demonstrated by Sprite Lamp so let's say it'll never happen. Then you need to code it, i.e. on which position of projectile use which variant of current animation's frame.
After you done with protagonist there's enemies, of course you can use positioning tricks with them to maximally reduce directions from which light might come and frames they have to reduce sprites and lighting profiles amount, which limits level and enemy design. If by that time you'll still a wish to utilize Sprite Lamp more in your game you might want to ad lighting effect to floor, walls, etc. If you up to it - you must do it by screen, not by tile if you have separated screens like in megaman, if you have scrolling like in mario you'll need to prerender whole level somehow with frames including every place where lighting projectiles could be.
All what I said about prerendering above stays true only if engine can't work with normal, ambient and depth maps, but as soon as we are talking on mugen forum I told it with it, fighter maker and open bor in mind - they can't do that, at least yet.
Summing that long-long post(with long-long sum up -_-''''').
Megaman is simple character and simple game(sprite-wise), but even with him you can lose your sanity making light profiles. And we talking about indie gaming where simpler graphics means better chances to game even make it to release. If you take it into mugen reality, making MvC/SNK-styled character with fixed light source for each frame is hard enough to spend years on it multiple it by 4 and no one in own mind will do this. Where it can be used - projectile shooting frames and projectile death animation, but it's really minor and not very noticeable.
Where can I see it's used like it is - something to accent player's attention on, like savepoint with light flying around it or legendary artifact or something like that, with no or minimal animation. There in games and in pixel-art pictures, where result indeed could be outstanding.
Of course it's thousand's time easier than to do by hand, but no one does it by hand. If you need 3 stages of lighting on 3 different animation frames you draw draw 3 frames and that's it, not 3 frames and 6 lighting profiles(not 12 as you probably need only front and back covered).
If it had someway to automate process of profiles creation, then it would be tool of future for 2d games. Only problem would be coding it and size. If personal projects might work with normal and depth maps(I'm not so knowledgeable in gamedev, but of popular engines paid version of unity probably have all needed features already in, dunno if game maker have anything like this) mugen and bor can't do that and bound to deal with tons of prerendered sprites and lots of code for them.
If by this point you still was with me you deserve a medal or something or I hypnotized you somehow
At this point I've doubted that I need to press "post" button, but as you see I've pressed it.
D-Day