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, ...

build up an array using formulas
 

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, ...


.

build up an array using formulas
 

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" wrote in 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, ...

build up an array using formulas
 

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" wrote in 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, ...