It's the basic building block (along with pure) for other very useful functions like liftA2 (which you could think of as "fmap over two containers" and not be too far off the mark):
liftA2 :: Applicative f => (a -> b -> c) -> f a -> f b -> f c
liftA2 f fa fb = pure f <*> fa <*> fb
So suppose you want to add all combinations of values of two lists xs, ys :: [Integer], you could do it this way:
liftA2 (+) xs ys
Though more commonly we write this:
(+) <$> xs <*> ys
You are right to be puzzled why would somebody ever write [(+),(*)] <*> [1,2] <*> [3,4]—I think it's a very artificial example. 99% of the time, Applicative is used, you fundamentally have one function that takes two or more arguments, and are looking to apply it to the values inside a functor that implements Applicative.
One way of looking at it is that we could alternatively define Applicative and associated functions like this:
-- Not the real definition
class Functor f => Applicative f where
pure :: a -> f a
-- Like `fmap` but for two-argument functions:
liftA2 :: (a -> b -> c) -> f a -> f b -> f c
-- `<*>` and `liftA2` are interdefinable, as shown further up and just here:
(<*>) :: Applicative f => f (a -> b) -> f a -> f b
ff <*> fa = liftA2 ($) ff fa
But the reason <*> is picked as the class method is that it makes it very easy to write liftA2, liftA3, etc.:
liftA2 :: Applicative f => (a -> b -> c) -> f a -> f b -> f c
liftA2 f fa fb = pure f <*> fa <*> fb
liftA3 :: Applicative f => (a -> b -> c -> d) -> f a -> f b -> f c -> f d
liftA3 f fa fb fc = pure f <*> fa <*> fb <*> fc
liftA4 :: Applicative f => (a -> b -> c -> d -> e) -> f a -> f b -> f c -> f d -> f e
liftA4 f fa fb fc fd = pure f <*> fa <*> fb <*> fc <*> fd
The Applicative class is very nearly the minimum functionality needed to support this "map with a function of multiple arguments" pattern, which is a useful thing in tons of cases.