Repeat in functions?

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(Schema!$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" <[email protected]> wrote in message
news:[email protected]...
>
> 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;Schema!D$1:Schema!D$400;0)-1;0));"-";FÖRSKJUTNING(Schema!$A$1;PASSA($B20;Schema!D$1:Schema!D$400;0)-1;0))
>
> --------------------
>
> or in english:
>
> Code:
> --------------------
>
>
>
> =IF(ISMISSING(DISPLACEMENT(Schema!A1;WATCH($B20;Schema!D$1:Schema!D$400;0)-1;0));"-";DISPLACEMENT(Schema!$A$1;WATCH($B20;Schema!D$1:Schema!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
>

It works great.
Here is your formula in Swedish:

Please Login or Register  to view this content.