Let's break it down using this data:

a1 port:rotterdam
a2 richard
a3 david
a4 paul
a5 port:south africa
a6 james

=IF(LEFT(A2,4)="port","",MID(LOOKUP(10,SEARCH("por t",A$1:A1),A$1:A1),6,255))

Everyone probably understands the IF(LEFT....) stuff so I'll skip that.

MID(LOOKUP(10,SEARCH("port",A$1:A1),A$1:A1),6,255)

SEARCH returns the starting position of a substring within a string. The
starting position is the character number. If the substring is not found
SEARCH returns a #VALUE! error. We're searching for the substring "port"
within the string indicated by the cell reference that grows into a range of
cells as we copy the formula down.

In the sample data that contains the substring "port" it's found at position
1. If a string contains multiple instances of the substring SEARCH will find
the *first* instance from left to right and return the starting position of
that *first* instance. So, with the sample data the result of SEARCH will
always be either 1 or #VALUE!.

With the formula entered in B2 and copied down this is what the SEARCH
function returns (V = #VALUE! error):

B2 = SEARCH("port",A$1:A1) = 1
B3 = SEARCH("port",A$1:A2) = {1;V}
B4 = SEARCH("port",A$1:A3) = {1;V;V}
B5 = "" blank due to IF(LEFT(....)
B6 = SEARCH("port",A$1:A5) = {1;V;V;V;1}

The results of the SEARCH function are then passed to the LOOKUP function.

B2 = LOOKUP(10,1,A$1:A1)
B3 = LOOKUP(10,{1;V},A$1:A2)
B4 = LOOKUP(10,{1;V;V},A$1:A3)
B5 = "" blank due to IF(LEFT(....)
B6 = LOOKUP(10,{1;V;V;V;1},A$1:A5)

Now comes the confusing part!!!!

The way that LOOKUP works is if the lookup_value is greater than any numeric
value in the lookup_vector, it will "match" the *last numeric* value in the
lookup_vector that is *less* than the lookup_value. The lookup_vector is the
result of the SEARCH function. Since the SEARCH function returned only
either 1 or V, the lookup_value (10) *is* greater than any numeric value in
the lookup_vector so it will "match" the *last numeric* value in the
lookup_vector.

LOOKUP returns the result from the result_vector that corresponds to *last
numeric* value in the lookup_vector that is *less* than the lookup_value.
So, this is what the lookup_vector and the result_vector look like as the
formula is copied down. The result of LOOKUP is the value in the
result_vector that corresponds to the *last* 1 in the lookup_vector:

LV = lookup_vector
RV = result_vector

B2::
LV............RV
1...............port:rotterdam

B3:
LV............RV
1..............port:rotterdam
V.............richard

B4:
LV...........RV
1..............port:rotterdam
V.............richard
V.............david

B5: "" blank due to IF(LEFT(....)

B6:
LV...........RV
1..............port:rotterdam
V.............richard
V.............david
V.............paul
1..............port:south africa

So:

B2 = port:rotterdam
B3 = port:rotterdam
B4 = port:rotterdam
B5 = ""
B6 = port:south africa


Then the result of the LOOKUP function is passed to the MID function where
we get rid of the "port:" at the beginning of the string.

So, the final result is:

B2: rotterdam
B3: rotterdam
B4: rotterdam
B5:
B6:south africa

That's a "deluxe" explanation! Hopefully it didn't make things more
confusing.



--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
I second Ron's request. My co-workers and I are completely baffled!!!

"Ron@Buy" wrote:

Brilliant !
Tried breaking down your formula to see how it works but got lost on the
10,
would appreciate a brief on how it produces the correct result.


"T. Valko" wrote:

Leave cell B1 empty.

Enter this formula in B2 and copy down as needed:

=IF(LEFT(A2,4)="port","",MID(LOOKUP(10,SEARCH("por t",A$1:A1),A$1:A1),6,255))


--
Biff
Microsoft Excel MVP


"Rich Hayes" wrote in message
...
Hi,

I have a query on some data i'm currently analysing and it's causing
me a
headache. I'm hoping there is a simple solution to this that doesn't
involve
macros.

Any help much appreciated

Sample data: this is what i have in one column of data at present.
each
name
beneath a port represents people working at that particular port.
However,
what i want is in the column along side this data for it to show the
port
name that each employee works at.

what i have at present is as follows;

cell a1 port:rotterdam
cell a2 richard
cell a3 david
cell a4 paul
cell a5 port:south africa
cell a6 james
cell a7 sam
cell a8 keith
cell a9 duncan
cell a10 port:port talbot
cell a11 simon
cell a12 rachel

what i'd like to see is a formula in column B to return the relevant
port
for each individual (shown below) It is a large document with over
5000
rows
so a formula is a must if possible.

column A column B
Port: rotterdam
Richard rotterdam
David rotterdam
Paul rotterdam
Port: south africa
james south africa
sam south africa
keith south africa
duncan south africa
Port: port talbot
simon port talbot
rachel port talbot