Given a range of cells in Excel, two columns and any number of rows,
with data that looks like the following:

1       no
5       no
10      no
20      no
35      yes
50      yes
100     yes

Column A will have increasing values. Column B will have zero or more
"no" values, which will at some point switch to "yes" values.

I want to have a cell which reports the switchover value. That is -
for the above range of cells, it would report 35.

If you are prepared to swap your two columns around (i.e. put the
yes/no's in column A), you can do the following:

Name your range (in this example I've called it theRange)

Then in C1 enter:

=VLOOKUP("yes", theRange, 2, FALSE)

The 2 means return the data from the 2nd column in your range.