Two Match Statements.
 

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

Two Match Statements.
 

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

Two Match Statements.
 

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