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.