This will work up to 5 occurences.
Use the array formula, entered with Ctrl-Shift-Enter, in C1, and copy to C1:D??:
(change the 10's to a number higher than your row count on Schema)
=TRIM(IF(COUNTIF(Schema!C$1:C$10,$B1)=5,INDEX(Sch ema!$B:$B,LARGE(((Schema!C$1:C$10=$B1)*ROW(Schema! C$1:C$10)),5)),"")
& " "
&IF(COUNTIF(Schema!C$1:C$10,$B1)=4,INDEX(Schema!$ B:$B,LARGE(((Schema!C$1:C$10=$B1)*ROW(Schema!C$1:C $10)),4)),"")
& " "
&IF(COUNTIF(Schema!C$1:C$10,$B1)=3,INDEX(Schema!$ B:$B,LARGE(((Schema!C$1:C$10=$B1)*ROW(Schema!C$1:C $10)),3)),"")
& " "
&IF(COUNTIF(Schema!C$1:C$10,$B1)=2,INDEX(Schema!$ B:$B,LARGE(((Schema!C$1:C$10=$B1)*ROW(Schema!C$1:C $10)),2)),"")
& " "
&IF(COUNTIF(Schema!C$1:C$10,$B1)=1,INDEX(Schema!$ B:$B,LARGE(((Schema!C$1:C$10=$B1)*ROW(Schema!C$1:C $10)),1)),""))
Note that this is the underlying "unit" formula:
IF(COUNTIF(Schema!C$1:C$10,$B1)=5,INDEX(Schema!$B :$B,LARGE(((Schema!C$1:C$10=$B1)*ROW(Schema!C$1:C$ 10)),5)
Where the 5 finds the 5th occurence... This will find the last five occurences. If you need to find
the first five occurences, then your need to change the second 5 to the formula (with the
progression)
COUNTIF(Schema!C$1:C$10,$B1)
COUNTIF(Schema!C$1:C$10,$B1) - 1
COUNTIF(Schema!C$1:C$10,$B1) - 2
HTH,
Bernie
MS Excel MVP
"Ruatha" wrote in message
...
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