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