Hi.
I've writte an function that looks for a word in column B and searches
for that word in a column in a different worksheet, when found it
returns a word found in column B in that row.

For example:

Worksheet "Schema":

A B C D
1 a hi zoo now
2 b go now zoo
3 c lo zoo zoo


worksheet "home":
A B C D
1 a zoo
2 b zoo
3 c now

Now the formula in worksheet "home" cell C1 should look for zoo
(content in B1) in column C in worksheet "Schema" and return "hi lo".
The formula in cell D1 should look for zoo (B1) in column D in "schema"
and return "go lo":
ie:
worksheet "home":
A B C D
1 a zoo hi lo go lo
2 b zoo hi lo go lo
3 c now go hi

this is the current formula:

Code:
--------------------


=OM(ÄRSAKNAD(FÖRSKJUTNING(Schema!A1;PASSA($B20;Sch ema!D$1:Schema!D$400;0)-1;0));"-";FÖRSKJUTNING(Schema!$A$1;PASSA($B20;Schema!D$1:S chema!D$400;0)-1;0))

--------------------

or in english:

Code:
--------------------


=IF(ISMISSING(DISPLACEMENT(Schema!A1;WATCH($B20;Sc hema!D$1:Schema!D$400;0)-1;0));"-";DISPLACEMENT(Schema!$A$1;WATCH($B20;Schema!D$1:S chema!D$400;0)-1;0))

--------------------



(My translation) (Displacement moves relativeley, WATCH finds the
content in one cell in another column)

Now the problem, it only finds the first occasion and only returns one
value, can it be repeated in some easy way to return every (up to 5)
occasions in the column.


--
Ruatha
------------------------------------------------------------------------
Ruatha's Profile: http://www.excelforum.com/member.php...o&userid=31083
View this thread: http://www.excelforum.com/showthread...hreadid=507578