I need to look up a value in a table based on 2 different criteria. The first
is the item code, and the second is the month. The "B", "C" and "D" column
headers are the month and the value that determines what column of the table
to look in, the table is laid out in a 24 month grid. The (month) value at
the top of the column "B" will change to the current month, every month and
subsequent columns will change accordingly.

Results Worksheet:

Product Code Feb 09 Mar 09 Apr 09
0050B 4 5 2
1008-3011-00-000 0 6 0
1008-3041-00-000 0 24 0
1008-4101-00-000 0 24 0



Table of Data:

ITEM JAN 09 FEB 09 MAR 09 APR 09
0050B 6 4 5 2
1008-3011-00-000 0 6 0 0
1008-3041-00-000 0 24 0 0
1008-4101-00-000 0 24 0 0

Ultimately I'm trying to develop a rolling 12 month report starting with the
current month and moving out from there. The current month will change as
time progresses. The data is supplied from a table containing 24 months of
data.

This can be done using INDEX with MATCH.

In your results table on cell B2:
=INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1,0))

As long as the dates at the tops of the columns are actually dates in B1 you
can use =DATE(year(today()),month(today()),1) (formatted as "mmm yy").
C1 would be =DATE(year(today()),month(today())+1,1)
D1 would be =DATE(year(today()),month(today())+2,1)

Otherwise, if they are just text, wrap that in a TEXT Function like:
=TEXT(DATE(year(today()),month(today()),1),"mmm yy")

Is that what you were hoping for?

Just make sure if the dates are dates that you use the first of the month in
all cases, or there won't be a match.

"Picman" wrote:

I need to look up a value in a table based on 2 different criteria. The first
is the item code, and the second is the month. The "B", "C" and "D" column
headers are the month and the value that determines what column of the table
to look in, the table is laid out in a 24 month grid. The (month) value at
the top of the column "B" will change to the current month, every month and
subsequent columns will change accordingly.

Results Worksheet:

Product Code Feb 09 Mar 09 Apr 09
0050B 4 5 2
1008-3011-00-000 0 6 0
1008-3041-00-000 0 24 0
1008-4101-00-000 0 24 0



Table of Data:

ITEM JAN 09 FEB 09 MAR 09 APR 09
0050B 6 4 5 2
1008-3011-00-000 0 6 0 0
1008-3041-00-000 0 24 0 0
1008-4101-00-000 0 24 0 0

Ultimately I'm trying to develop a rolling 12 month report starting with the
current month and moving out from there. The current month will change as
time progresses. The data is supplied from a table containing 24 months of
data.

This didn't work for me, I received an error. This is the formula after I
adjusted it to my parameters.

=INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER
ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))

"CUSTOMER ORDER" is the worksheet that contains the data. It is a simple 25
column wide by 371 rows tall table with the first row containing column
headers.

On my original submission I did not show all of the columns of my results
worksheet because of space issues. "A3" contains the value to search for on
the row of the data table and "F2" contains the reference value to determine
the column.


"~L" wrote:

This can be done using INDEX with MATCH.

In your results table on cell B2:
=INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1,0))

As long as the dates at the tops of the columns are actually dates in B1 you
can use =DATE(year(today()),month(today()),1) (formatted as "mmm yy").
C1 would be =DATE(year(today()),month(today())+1,1)
D1 would be =DATE(year(today()),month(today())+2,1)

Otherwise, if they are just text, wrap that in a TEXT Function like:
=TEXT(DATE(year(today()),month(today()),1),"mmm yy")

Is that what you were hoping for?

Just make sure if the dates are dates that you use the first of the month in
all cases, or there won't be a match.

"Picman" wrote:

I need to look up a value in a table based on 2 different criteria. The first
is the item code, and the second is the month. The "B", "C" and "D" column
headers are the month and the value that determines what column of the table
to look in, the table is laid out in a 24 month grid. The (month) value at
the top of the column "B" will change to the current month, every month and
subsequent columns will change accordingly.

Results Worksheet:

Product Code Feb 09 Mar 09 Apr 09
0050B 4 5 2
1008-3011-00-000 0 6 0
1008-3041-00-000 0 24 0
1008-4101-00-000 0 24 0



Table of Data:

ITEM JAN 09 FEB 09 MAR 09 APR 09
0050B 6 4 5 2
1008-3011-00-000 0 6 0 0
1008-3041-00-000 0 24 0 0
1008-4101-00-000 0 24 0 0

Ultimately I'm trying to develop a rolling 12 month report starting with the
current month and moving out from there. The current month will change as
time progresses. The data is supplied from a table containing 24 months of
data.

=INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER
ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))

has an extra ,0, before the first MATCH. I would also make the whole lookup
range absolute:

=INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER
ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))


"Picman" wrote:

This didn't work for me, I received an error. This is the formula after I
adjusted it to my parameters.

=INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER
ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))

"CUSTOMER ORDER" is the worksheet that contains the data. It is a simple 25
column wide by 371 rows tall table with the first row containing column
headers.

On my original submission I did not show all of the columns of my results
worksheet because of space issues. "A3" contains the value to search for on
the row of the data table and "F2" contains the reference value to determine
the column.


"~L" wrote:

This can be done using INDEX with MATCH.

In your results table on cell B2:
=INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1,0))

As long as the dates at the tops of the columns are actually dates in B1 you
can use =DATE(year(today()),month(today()),1) (formatted as "mmm yy").
C1 would be =DATE(year(today()),month(today())+1,1)
D1 would be =DATE(year(today()),month(today())+2,1)

Otherwise, if they are just text, wrap that in a TEXT Function like:
=TEXT(DATE(year(today()),month(today()),1),"mmm yy")

Is that what you were hoping for?

Just make sure if the dates are dates that you use the first of the month in
all cases, or there won't be a match.

"Picman" wrote:

I need to look up a value in a table based on 2 different criteria. The first
is the item code, and the second is the month. The "B", "C" and "D" column
headers are the month and the value that determines what column of the table
to look in, the table is laid out in a 24 month grid. The (month) value at
the top of the column "B" will change to the current month, every month and
subsequent columns will change accordingly.

Results Worksheet:

Product Code Feb 09 Mar 09 Apr 09
0050B 4 5 2
1008-3011-00-000 0 6 0
1008-3041-00-000 0 24 0
1008-4101-00-000 0 24 0



Table of Data:

ITEM JAN 09 FEB 09 MAR 09 APR 09
0050B 6 4 5 2
1008-3011-00-000 0 6 0 0
1008-3041-00-000 0 24 0 0
1008-4101-00-000 0 24 0 0

Ultimately I'm trying to develop a rolling 12 month report starting with the
current month and moving out from there. The current month will change as
time progresses. The data is supplied from a table containing 24 months of
data.

Still not working for me. Let me varify that I know what the formula is
doing. The "Index" section is indexing the data table, the 1st "Match" is
finding the appropriate row to look in, and the 2nd "Match" if finding the
column to look in. Am I correct?

"~L" wrote:

=INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER
ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))

has an extra ,0, before the first MATCH. I would also make the whole lookup
range absolute:

=INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER
ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))


"Picman" wrote:

This didn't work for me, I received an error. This is the formula after I
adjusted it to my parameters.

=INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER
ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))

"CUSTOMER ORDER" is the worksheet that contains the data. It is a simple 25
column wide by 371 rows tall table with the first row containing column
headers.

On my original submission I did not show all of the columns of my results
worksheet because of space issues. "A3" contains the value to search for on
the row of the data table and "F2" contains the reference value to determine
the column.


"~L" wrote:

This can be done using INDEX with MATCH.

In your results table on cell B2:
=INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1,0))

As long as the dates at the tops of the columns are actually dates in B1 you
can use =DATE(year(today()),month(today()),1) (formatted as "mmm yy").
C1 would be =DATE(year(today()),month(today())+1,1)
D1 would be =DATE(year(today()),month(today())+2,1)

Otherwise, if they are just text, wrap that in a TEXT Function like:
=TEXT(DATE(year(today()),month(today()),1),"mmm yy")

Is that what you were hoping for?

Just make sure if the dates are dates that you use the first of the month in
all cases, or there won't be a match.

"Picman" wrote:

I need to look up a value in a table based on 2 different criteria. The first
is the item code, and the second is the month. The "B", "C" and "D" column
headers are the month and the value that determines what column of the table
to look in, the table is laid out in a 24 month grid. The (month) value at
the top of the column "B" will change to the current month, every month and
subsequent columns will change accordingly.

Results Worksheet:

Product Code Feb 09 Mar 09 Apr 09
0050B 4 5 2
1008-3011-00-000 0 6 0
1008-3041-00-000 0 24 0
1008-4101-00-000 0 24 0



Table of Data:

ITEM JAN 09 FEB 09 MAR 09 APR 09
0050B 6 4 5 2
1008-3011-00-000 0 6 0 0
1008-3041-00-000 0 24 0 0
1008-4101-00-000 0 24 0 0

Ultimately I'm trying to develop a rolling 12 month report starting with the
current month and moving out from there. The current month will change as
time progresses. The data is supplied from a table containing 24 months of
data.

Are your entries in B1:Y1 proper dates, or are they text values? What
value do you have in F2 to specify the month in question?

Pete

On Feb 18, 4:45*pm, Picman wrote:
Still not working for me. Let me varify that I know what the formula is
doing. The "Index" section is indexing the data table, the 1st "Match" is
finding the appropriate row to look in, and the 2nd "Match" if finding the
column to look in. Am I correct?



"~L" wrote:
*=INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER
ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))

has an extra ,0, before the first MATCH. *I would also make the whole lookup
range absolute:

*=INDEX('CUSTOMER ORDER'!$A$1:$Y$371,MATCH($A3,'CUSTOMER
ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))

"Picman" wrote:

This didn't work for me, I received an error. This is the formula after I
adjusted it to my parameters.

=INDEX('CUSTOMER ORDER'!$A$1:$Y371,0,MATCH($A3,'CUSTOMER
ORDER'!$A$1:$A$371,0),MATCH(F$2,'CUSTOMER ORDER'!$A$1:$Y$1,0))

"CUSTOMER ORDER" is the worksheet that contains the data. It is a simple 25
column wide by 371 rows tall table with the first row containing column
headers.

On my original submission I did not show all of the columns of my results
worksheet because of space issues. "A3" contains the value to search for on
the row of the data table and "F2" contains the reference value to determine
the column.

"~L" wrote:

This can be done using INDEX with MATCH.

In your results table on cell B2:
=INDEX(Data!$A$1:$X50,match($A2,Data!$A$1:$A$50,0) ,match(B$1,Data!$A$1:$X$1*,0))

As long as the dates at the tops of the columns are actually dates in B1 you
can use =DATE(year(today()),month(today()),1) *(formatted as "mmm yy").
C1 would be =DATE(year(today()),month(today())+1,1)
D1 would be =DATE(year(today()),month(today())+2,1)

Otherwise, if they are just text, wrap that in a TEXT Function like:
=TEXT(DATE(year(today()),month(today()),1),"mmm yy")

Is that what you were hoping for?

Just make sure if the dates are dates that you use the first of the month in
all cases, or there won't be a match.

"Picman" wrote:

I need to look up a value in a table based on 2 different criteria. The first
is the item code, and the second is the month. The "B", "C" and "D" column
headers are the month and the value that determines what column of the table
to look in, the table is laid out in a 24 month grid. The (month) value at
the top of the column "B" will change to the current month, every month and
subsequent columns will change accordingly.

Results Worksheet:

Product Code * * * * * *Feb 09 * * *Mar 09 *Apr 09
0050B * * * * * * * * * * *4 * * * *5 * * * 2
1008-3011-00-000 * * 0 * * *6 * * * 0
1008-3041-00-000 * * 0 * * *24 * * *0
1008-4101-00-000 * * 0 * * *24 * * *0

Table of Data:

ITEM * * * * * * * * * * * *JAN 09 *FEB 09 *MAR 09 *APR 09
0050B * * * * * * * * * * * 6 * * * 4 * * * 5 * * * 2
1008-3011-00-000 * * * * * *0 * * * 6 * * * 0 * * * 0
1008-3041-00-000 * * * * * *0 * * * 24 * * *0 * * * 0
1008-4101-00-000 * * * * * *0 * * * 24 * * *0 * * * 0

Ultimately I'm trying to develop a rolling 12 month report starting with the
current month and moving out from there. The current month will change as
time progresses. The data is supplied from a table containing 24 months of
data.- Hide quoted text -

- Show quoted text -