Hello,

I'm hoping that someone can help me with a problem I am
having.

I have a massive table of data for each month that lists
information for 30 variables. In my excel file that I am
working on, I use a match statement to find the
appropriate ID of the item I am looking for in a given
array for the month I am interested in (since there are
about 200 months worth of data in my data sheet, I could
feasibly get 200 different results). This is however VERY
inefficient, and I would like to somehow get the data I
want for a given month without having to change my
formulas.

For example:

I know that Month One starts at row 1 and ends at 32. I
use a match statement to figure out when the next month
starts (34--64) and so on. However, since I have to
specify my array in the formula, unless I manually update
the formula to reflect that I am now searching for ID in
A34:A64 rather than A1:A32, I will (of course) receive the
row # for the month I do not want.

Can anyone help me out and explain how I can somehow
search first for the appropriate date, and secondly for
the data ID code given that appropriate date, and finally
return the value I want? I would call it some sort of
conditional MATCH statement?

Thanks very much,

David

It will facilitate an answer if you repost indicating how "Month One"
actually appears in your data; and if you note that 1 to 32 is 32
entries, 34 to 64 is 31 entries.

Alan Beban

David wrote:

Hello,

I'm hoping that someone can help me with a problem I am
having.

I have a massive table of data for each month that lists
information for 30 variables. In my excel file that I am
working on, I use a match statement to find the
appropriate ID of the item I am looking for in a given
array for the month I am interested in (since there are
about 200 months worth of data in my data sheet, I could
feasibly get 200 different results). This is however VERY
inefficient, and I would like to somehow get the data I
want for a given month without having to change my
formulas.

For example:

I know that Month One starts at row 1 and ends at 32. I
use a match statement to figure out when the next month
starts (34--64) and so on. However, since I have to
specify my array in the formula, unless I manually update
the formula to reflect that I am now searching for ID in
A34:A64 rather than A1:A32, I will (of course) receive the
row # for the month I do not want.

Can anyone help me out and explain how I can somehow
search first for the appropriate date, and secondly for
the data ID code given that appropriate date, and finally
return the value I want? I would call it some sort of
conditional MATCH statement?

Thanks very much,

David

.... This is however VERY
inefficient, and I would like to somehow get the data I
want for a given month without having to change my
formulas.

If it's already very inefficient now.. reconsider.

Redesigning HOW the data is stored can save you a lot of hassle later on.
With your data in a proper 'database' structure, rewriting your formulas
will be easy. and maintenance / analysis options will improve drastically.


Year,Month,Dept

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"David" wrote:

Hello,