build up an array using formulas

I want to see in a variable range (A) if a certain value occurs.
The first and the last cell of range A are determined by other values.

For example

In col 1 I got dates from 01/01/2005 till 31/12/2005 (DD/MM/YYYY)
In col 2 I got values like "CC", "VV", ... or nothing

In col 4 till 7 I got something like this
from till
0 01 January 2005 08 January 2005
1 09 January 2005 05 February 2005

In col 7 I would like to find the cell in col 2 where the value = "CC"
within the range defined by the from and till fields.
The value "CC" may normally only occur once in this range.

So I would need a function or something that translates the "from - till"
fields in a range of col 2 and than look for the value "CC" in this range and
return the date from col 1 on this row.

Can someone help me out ?
I already tried several combinations on match, index, lookup, ...

Hi Kezze,

do you need to find a formula-based solution for your task
or would VBA also be possible?

If you need a cell formula only, I could imagine about
some database functions... but I'm not firm on that...

With VBA, this problem should be very solvable... would
that be okay too?


Best

Markus

>-----Original Message-----
>I want to see in a variable range (A) if a certain value
occurs.
>The first and the last cell of range A are determined by
other values.
>
>For example
>
>In col 1 I got dates from 01/01/2005 till 31/12/2005
(DD/MM/YYYY)
>In col 2 I got values like "CC", "VV", ... or nothing
>
>In col 4 till 7 I got something like this
> from till
>0 01 January 2005 08 January 2005
>1 09 January 2005 05 February 2005
>
>In col 7 I would like to find the cell in col 2 where the
value = "CC"
>within the range defined by the from and till fields.
>The value "CC" may normally only occur once in this range.
>
>So I would need a function or something that translates
the "from - till"
>fields in a range of col 2 and than look for the
value "CC" in this range and
>return the date from col 1 on this row.
>
>Can someone help me out ?
>I already tried several combinations on match, index,
lookup, ...
>
>
>.
>

Assme Start Date in E1 and End Date in F1 then:

=LARGE((($A$1:$A$100>=E1)*($A$1:$A$100<=F1)*($B$1:$B$100="CC")*ROW($B$1:$B$1
00)),1)

Entered with Ctrl+Shift+Enter rather than enter since this is an array
formula.


--
Regards,
Tom Ogilvy

"Kezze" <[email protected]> wrote in message
news:[email protected]...
> I want to see in a variable range (A) if a certain value occurs.
> The first and the last cell of range A are determined by other values.
>
> For example
>
> In col 1 I got dates from 01/01/2005 till 31/12/2005 (DD/MM/YYYY)
> In col 2 I got values like "CC", "VV", ... or nothing
>
> In col 4 till 7 I got something like this
> from till
> 0 01 January 2005 08 January 2005
> 1 09 January 2005 05 February 2005
>
> In col 7 I would like to find the cell in col 2 where the value = "CC"
> within the range defined by the from and till fields.
> The value "CC" may normally only occur once in this range.
>
> So I would need a function or something that translates the "from - till"
> fields in a range of col 2 and than look for the value "CC" in this range
and
> return the date from col 1 on this row.
>
> Can someone help me out ?
> I already tried several combinations on match, index, lookup, ...
>
>

Thanks Tom,
Exactly what I needed.
I think I should investigate some more on the possibility's of array formulas

"Tom Ogilvy" wrote:

> Assme Start Date in E1 and End Date in F1 then:
>
> =LARGE((($A$1:$A$100>=E1)*($A$1:$A$100<=F1)*($B$1:$B$100="CC")*ROW($B$1:$B$1
> 00)),1)
>
> Entered with Ctrl+Shift+Enter rather than enter since this is an array
> formula.
>
>
> --
> Regards,
> Tom Ogilvy
>
> "Kezze" <[email protected]> wrote in message
> news:[email protected]...
> > I want to see in a variable range (A) if a certain value occurs.
> > The first and the last cell of range A are determined by other values.
> >
> > For example
> >
> > In col 1 I got dates from 01/01/2005 till 31/12/2005 (DD/MM/YYYY)
> > In col 2 I got values like "CC", "VV", ... or nothing
> >
> > In col 4 till 7 I got something like this
> > from till
> > 0 01 January 2005 08 January 2005
> > 1 09 January 2005 05 February 2005
> >
> > In col 7 I would like to find the cell in col 2 where the value = "CC"
> > within the range defined by the from and till fields.
> > The value "CC" may normally only occur once in this range.
> >
> > So I would need a function or something that translates the "from - till"
> > fields in a range of col 2 and than look for the value "CC" in this range
> and
> > return the date from col 1 on this row.
> >
> > Can someone help me out ?
> > I already tried several combinations on match, index, lookup, ...
> >
> >
>
>
>