Searching for multiple results in one cell
 

I am trying to plot start and end dates. I am able to do this although I am
using a table that can have multiple entries to be plotted.

I am using this formula:
=IF(B$1=INDEX($B$13:$B$20,MATCH($A2,$A$13:$A$20,0) ),"S",IF(B$1=INDEX($C$13:$C$20,MATCH($A2,$A$13:$A$ 20,1)),"E",IF(AND(B$1INDEX($B$13:$B$20,MATCH($A2, $A$13:$A$20,0)),B$1<INDEX($C$13:$C$20,MATCH($A2,$A $13:$A$20,1))),"M","-")))

This is a nested IF that will search the List of names for a match, it will
then try and match the start date, then end date and finally if the date is
in between the two.

The problem I have is AA for example will have 2 entries, I want to plot
both Entries on a particular row although after the matches finds the first
Match, it stops looking.

Is there anyway to have a formula where is will look for a match, and
continue looking through out the array.

In a programming sense I would want an IF Loop Until it reaches end of array
or meets critera.

Below is an illustration of the table.

1 2 3 4 5 6
AA S M E - - -
BB - S M M E -
CC - - - - S M
DD - - S M M E
EE - S M M M E
FF - E S - - -
CC - - - - S M



Name Sdate Edate
AA 1 3
BB 2 5
CC 5 8
DD 3 6
EE 2 6
FF 3 2
AA 1 6
CC 1 5

Searching for multiple results in one cell
 

Try the following...

1) First, define the following reference...

Select B2 (needs to be the active cell)

Insert Name Define

Name: RowNum

Refers to:

=SMALL(IF(Sheet1!$A$13:$A$20=Sheet1!$A2,ROW(Sheet1 !$A$13:$A$20)-ROW(Sheet
1!$A$13)+1),COUNTIF(Sheet1!$A$2:$A2,Sheet1!$A2))

Click Ok

2) Enter the following formula in B2, copy across, and down:

=IF(B$1=INDEX($B$13:$B$20,RowNum),"S",IF(B$1=INDEX ($C$13:$C$20,RowNum),"E
",IF(AND(B$1INDEX($B$13:$B$20,RowNum),B$1<INDEX($ C$13:$C$20,RowNum)),"M"
,"-")))

Hope this helps!

In article ,
VLB wrote:

I am trying to plot start and end dates. I am able to do this although I am
using a table that can have multiple entries to be plotted.

I am using this formula:
=IF(B$1=INDEX($B$13:$B$20,MATCH($A2,$A$13:$A$20,0) ),"S",IF(B$1=INDEX($C$13:$C$
20,MATCH($A2,$A$13:$A$20,1)),"E",IF(AND(B$1INDEX( $B$13:$B$20,MATCH($A2,$A$13:
$A$20,0)),B$1<INDEX($C$13:$C$20,MATCH($A2,$A$13:$A $20,1))),"M","-")))

This is a nested IF that will search the List of names for a match, it will
then try and match the start date, then end date and finally if the date is
in between the two.

The problem I have is AA for example will have 2 entries, I want to plot
both Entries on a particular row although after the matches finds the first
Match, it stops looking.

Is there anyway to have a formula where is will look for a match, and
continue looking through out the array.

In a programming sense I would want an IF Loop Until it reaches end of array
or meets critera.

Below is an illustration of the table.

1 2 3 4 5 6
AA S M E - - -
BB - S M M E -
CC - - - - S M
DD - - S M M E
EE - S M M M E
FF - E S - - -
CC - - - - S M



Name Sdate Edate
AA 1 3
BB 2 5
CC 5 8
DD 3 6
EE 2 6
FF 3 2
AA 1 6
CC 1 5