davidm wrote...
....
. . . . Isn't the effectiveness of the search
(by the formula) critically dependent upon the data being "ordered or
srorted"?

Only when searching for values equal to or less than the largest value
in the range or array searched. If the value sought is larger than the
largest value in the range or array searched, then the initial
bracketting step of the lookup or match function returns immediately
with a reference to the last value in the range or array searched. That
is, these functions work as follows.

If lookupvalue < FirstValueInLookupRangeOrArray Then
return #N/A
ElseIf lookupvalue LastValueInLookupRangeOrArray Then
return reference to LastValueInLookupRangeOrArray
Else
search between first and last entries . . .
End If

In the orignial sample by Roger, the string was *Pharma-e,, ,Albert
House, South Esplanade, Guernsey, Channel Islands,,GY1 1AN ,(014816 ),
,Pharmacy, , ,202268*and this is by no means ordred or sorted. If the
erratic nature of LOOKUP with unsorted data is anything to go by, is
there not a danger that uncritical application of the formula may (at
times) lead to errorneous results? Albeilt,for some of the time we
might yet get correct results, the rub is zero tolerance in striving
for accuracy with no room for the hit and miss

Any time you pass an unsorted lookup table to {V|H|}LOOKUP with 4th
argument missing, TRUE or nonzero numeric, you could get inaccurate
results. The sole exception, given how Excel works now (all versions at
least from Excel 97 through Excel 2003, but likely all versions back to
Excel 1.0 for Macs), is when the lookup value is strictly greater than
the largest value of the same type in the range or array searched. In
that situation, Excel *consistently* returns a reference to that last
match.

I can offer no proof that this will always work because I don't have
Excel's source code handy to see exactly how Excel does this. However,
I'm comfortable with the empirical basis for this behavior. This has
become a widely used idiom. If it didn't work, I'm very confident
someone would have posted an example where it failed by now. No one
has, so I'm willing to rely on this functionality myself. You're free
not to do so if that'd make you more comfortable.