Importance of Least Upper Bound Property of $\mathbb{R}$
Solution 1:
One way to spot the importance of the Least Upper Bound property is to think about results that work in $\mathbb{R}$ but fail in $\mathbb{Q}$; this, because $\mathbb{Q}$ still has a lot of the algebraic properties of $\mathbb{R}$ (including the Archimedean property), but does not satisfy the Least Upper Bound property. So results that are true in $\mathbb{R}$ but are false in $\mathbb{Q}$ are good candidates for needing the Least Upper Bound property.
Some examples:
The Intermediate Value Theorem uses the Least Upper Bound Property of $\mathbb{R}$ (in fact, the IVT is equivalent to the Least Upper Bound Property; see below).
The fact that Cauchy sequences converge in $\mathbb{R}$ depends on the Least Upper Bound Property; without it, you can have sequences that are Cauchy but do not converge (as you do with $\mathbb{Q}$. That Cauchy sequences converge is very important in, for example, the definition of integration as limits of Riemann sums.
Bounded sequences have convergent subsequences (the Bolzano-Weierstrass Theorem); the proof in $\mathbb{R}$ rests on showing that such a sequence has a monotone subsequence, and the proof that monotone bounded sequences converge uses the Least Upper Bound Property directly.
The equivalence of the IVT and the Least Upper Bound Property:
IVT. Let $f\colon [a,b]\to\mathbb{R}$ be a continuous function. If $f(a)\lt 0$ and $f(b)\gt 0$, then there exists $c\in(a,b)$ such that $f(c)=0$.
Least Upper Bound Property. Let $S\subseteq \mathbb{R}$ be a nonempty set that is bounded above. Then $S$ has a least upper bound bound in $\mathbb{R}$.
Theorem. IVT is equivalent to the Least Upper Bound Property.
Proof. Assume the Least Upper Bound Property, and let $f\colon [a,b]\to\mathbb{R}$ be continuous. let $S=\{x\in [a,b]\mid f(x)\lt 0\}$. Then $S$ is bounded above by $b$, and is nonempty, since $a\in S$. Let $c$ be the least upper bound for $S$. Note that $a\lt c\lt b$.
If $f(c)\neq 0$, then there exists $\delta\gt 0$ such that for all $x$, $|x-c|\lt\delta$, $|f(x)-f(c)|\lt \frac{|f(c)|}{2}$. In particular, $f(x)$ and $f(c)$ have the same sign.
But: if $f(c)\gt 0$, then this means that for all $\delta\gt 0$ there exists $s\in S$ such that $c-\delta \lt s\leq c$, so $f(s)\lt 0$, hence $|f(s)-f(c)| = f(c)+|f(s)|\gt \frac{f(c)}{2}$, a contradiction. In particular, $c\lt b$.
And if $f(c)\lt 0$, then there exists $x\in [a,b]$ with $x\lt c+\delta$ (since $c$ cannot be $b$), and then $f(x)\lt 0$, so $x\in S$, contradicting the fact that $c$ is an upper bound for $S$.
Therefore, we cannot have $f(c)\neq 0$, so $f(c)=0$, with $a\lt c\lt b$, and we are done.
(The following is an argument suggested to me some years ago by Lee Rudolph on sci.math
) Conversely, assume that we have the Intermediate Value Theorem. Let $S$ be a nonempty subset of $\mathbb{R}$ that is bounded above; let $Y$ be the set of upper bounds of $S$, and define $f$ as follows:
$$f(x) = \left\{\begin{array}{ll}
1 & x\in Y\\
-1 & x\notin Y
\end{array}\right.$$
Note that there are points where $f$ takes value $-1$, and points where $f$ takes value $1$, but there are no point where $f$ takes the value $0$. That means, by the Intermediate Value Theorem, that $f$ is not continuous. Therefore, there is a point $z$ where $f$ is not continuous.
If there exists $s\in S$ such that $z\lt s$, then letting $\delta=s-z\gt 0$ we have that $(z-\delta,z+\delta)\cap Y = \emptyset$, and therefore $f$ is constant on this interval, hence continuous. So no such $s$ can exist, and therefore $z$ is an upper bound for $S$.
If there exists $y\in Y$ such that $y\lt z$, then letting $\delta=z-y\gt 0$ we have that $(z-\delta,z+\delta)\subseteq Y$, and so $f$ is constant on this interval, hence continuous. This contradiction tells us that no such $y$ can exist either.
So $z$ is in $Y$ and is a lower bound for $Y$, hence $z$ is the least element of $Y$. Thus, $z$ is the least upper bound of $S$, and $S$ has a least upper bound, as desired. QED
Solution 2:
The importance is not so much because of the fact that the least upper bound property itself can be used directly to prove major thoerems. As Gerry pointed out, the important point is that it says that the field of real numbers is complete, which is how it is distinguished from the field of rational numbers, for example. Consequences of completeness, however it is formulated, are what is behind all of the analysis that would be impossible with $\mathbb Q$. Compactness of closed and bounded intervals, connectedness of intervals, convergence of bounded monotone sequences, convergence of Cauchy sequences, nonemptiness of intersections of nested sequences of closed bounded intervals, and existence of limit points of bounded infinite subsets, are some of the important properties in analysis that $\mathbb R$ has and $\mathbb Q$ doesn't, and all are consequences of some formulation of completeness. (Contrast this with the Archimedean property, which also holds for $\mathbb Q$.)
One could take other axioms as a starting point for analysis. For example, completeness for metric spaces is formulated in terms of Cauchy sequences, mentioned in Arturo's and Thomas's answers, and that can also be done for $\mathbb R$. There is one difference, however, which is that convergence of Cauchy sequences is not enough to guarantee the Archimedean property, so the latter would have to be taken as an additional axiom. Arturo's answer shows that the intermediate value property for continuous functions on intervals could also be taken as the defining axiom. I have seen at least one analysis textbook, Körner's excellent A companion to analysis, that makes convergence of bounded monotone sequences the defining axiom of the real numbers instead of the least upper bound property; the equivalence of the two is the topic of a recent question here.
Solution 3:
I think that just the fact that we define almost everything in analysis in terms of limits in enough to see that this is fundamental ; since one way to define limits is to say that the inferior limit is equal to the superior limit, we need those somehow implicitly to speak of limits, and thus need the completeness axiom.
But more explicitly, you can think about the theorem that states that monotone increasing bounded above sequences converges : what you do is consider $$ \alpha = \sup_{n \in \mathbb N} \{ x_n \} $$ which exists by the completeness axiom. Then you use $\delta$'s and $\varepsilon$'s to show that $\alpha$ is actually the limit.
Another example would be the definition of $n^{\text{th}}$ roots in analysis, which are defined in terms of supremums. Basically everything you know that is defined in terms of supremums uses that axiom implicity.
Hope that helps,