I think an important motivation for the $\ell$-adic theory comes from the Riemann existence theorem/the Grauert–Remmert theorem. This says that a finite (topological) covering space $Y \to X$ of a normal complex variety can again be equipped with an algebraic structure, which is more or less what you need to prove to obtain $$\pi_1^{\operatorname{\acute et}}(X) = \widehat{\pi_1^{\operatorname{top}}(X(\mathbf C))}.$$ So finite covering spaces can be detected using the étale topology (except it's not really a topology, but that's not stopping Grothendieck!).
In particular, you expect to get a good theory of étale cohomology with finite coefficients. But to run the arguments that Weil was dreaming of, you need characteristic $0$ coefficients, so what do you do? Just take a limit!
I think that's really the explanation of why the adic formalism enters the picture. But it doesn't quite explain why it's different at the prime $p$. There are a few ways to look at this:
- Serre's argument shows that a $\mathbf Q_p$-valued Weil cohomology theory cannot exist.
- The basic results on $\ell$-adic étale cohomology take the Galois cohomology of function fields of curves over algebraically closed fields as a starting point, and these behave differently at the prime $p$.
- Already for elliptic curves, the $p$-adic Tate module behaves a little different from the $\ell$-adic one.
In the end it doesn't really matter, because all they needed was one Weil cohomology theory.