I have read it quite some time back and forgotten its title. It is too hard to find via DDG, Google and MangaDex advanced search (tags tried include various combinations of: action, alien, drama, girl’s love, magical girls, monster, superhero, tragedy).

I believe the manga started off focusing on a boy who wants to be a superhero to protect the world, but couldn’t due to a lack of superpowers.

He ended up joining an agency that supports superheroes behind the frontlines, that initially consists of two sisters. The younger sister used to be a magical girl, but retired after losing her powers. She was very clingy to her older sister.

Later in the manga,

spoiler

the younger sister was still able to tap into her magical girl powers to stop the final villain, at the cost of her ability to walk.

It referenced / parodied a lot of other superheroes or superhero tropes, such as ultraman, superman, a hornet? dude (whose looks may be based off of spiderman) and a speedster.

At one point,

spoiler

the speedster reenacted the MCU quicksilver scene by dying to protect the hornet guy.

There was also a spark guy who tried to became more powerful, and ended up being manipulated by the final villain in a monster-like form. He ended up being killed by the daughter of the superman as his condition was irreversible, leaving behind a wife and young daughter.

The ending of the manga involved

spoiler

an ex-disciple of the superman killing him, revealing himself to be the final villain. He has powers related to biological cells, and has been using it to manipulate various people / monsters. The remaining superheroes teamed up to defeat him.

The manga finally ends with kamen rider going to the agency for advice, complaing that there is nothing for him to do as the world is at peace.

---

Edit

Found it!

It is Gamma.

I found it by using AniList advanced search with the following tags:

• genres: superhero, yuri
• format: manga
• publishing status: finished
• country of origin: japan
• minimum date percentage: 18%
• year range: 2005 to 2022

Looks like the tags on MangaDex are rather incomplete.

Thanks a lot to everyone who helped.