Referencing with multiple criteria
 

Hi all,
Im making a table that is referencing another set of data. The x-range of
the data is in months ranging from (Feb-06-Nov-08) on the Y axis is (Items
1-10). Both the table and the other set of data i am hoping to reference
have a varying XY axis. I would like to write a reference function that
would put the Date with the correct item. It should look as follows (scaled
down).

(REFERENCE THIS)
Feb-06 Mar-06 Apr-06 Feb-07 Dec-07
IT 1 0.86 1.00 1.14 1.49 1.71
IT 2 0.51 0.60 0.68 0.89 1.02
IT 3 0.04 0.05 0.06 0.07 0.08
IT 4 0.50 0.59 0.67 0.87 1.01
IT 5 0.07 0.08 0.09 0.11 0.13

(SHOULD LOOK LIKE)
Feb-06 Feb-06 Apr-06 Dec-07 Feb-07
IT 1 0.86 0.86 1.14 1.71 1.49
IT 1 0.86 0.86 1.14 1.71 1.49
IT 3 0.04 0.04 0.06 0.08 0.07
....ETC

THANKS

Referencing with multiple criteria
 

would i be using an OFFSET MATCH combination? anyone?

"Dave" wrote:

Hi all,
Im making a table that is referencing another set of data. The x-range of
the data is in months ranging from (Feb-06-Nov-08) on the Y axis is (Items
1-10). Both the table and the other set of data i am hoping to reference
have a varying XY axis. I would like to write a reference function that
would put the Date with the correct item. It should look as follows (scaled
down).

(REFERENCE THIS)
Feb-06 Mar-06 Apr-06 Feb-07 Dec-07
IT 1 0.86 1.00 1.14 1.49 1.71
IT 2 0.51 0.60 0.68 0.89 1.02
IT 3 0.04 0.05 0.06 0.07 0.08
IT 4 0.50 0.59 0.67 0.87 1.01
IT 5 0.07 0.08 0.09 0.11 0.13

(SHOULD LOOK LIKE)
Feb-06 Feb-06 Apr-06 Dec-07 Feb-07
IT 1 0.86 0.86 1.14 1.71 1.49
IT 1 0.86 0.86 1.14 1.71 1.49
IT 3 0.04 0.04 0.06 0.08 0.07
...ETC

THANKS

Referencing with multiple criteria
 

So your "should look like" table is going to have categories in column 1 and
dates in row 1. Then you want to use formulas to get the value that goes in the
data portion of the table (the intersection of that row and column)?

If yes, then you could use =index(match()).

Take a look at Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions03.html

Dave wrote:

Hi all,
Im making a table that is referencing another set of data. The x-range of
the data is in months ranging from (Feb-06-Nov-08) on the Y axis is (Items
1-10). Both the table and the other set of data i am hoping to reference
have a varying XY axis. I would like to write a reference function that
would put the Date with the correct item. It should look as follows (scaled
down).

(REFERENCE THIS)
Feb-06 Mar-06 Apr-06 Feb-07 Dec-07
IT 1 0.86 1.00 1.14 1.49 1.71
IT 2 0.51 0.60 0.68 0.89 1.02
IT 3 0.04 0.05 0.06 0.07 0.08
IT 4 0.50 0.59 0.67 0.87 1.01
IT 5 0.07 0.08 0.09 0.11 0.13

(SHOULD LOOK LIKE)
Feb-06 Feb-06 Apr-06 Dec-07 Feb-07
IT 1 0.86 0.86 1.14 1.71 1.49
IT 1 0.86 0.86 1.14 1.71 1.49
IT 3 0.04 0.04 0.06 0.08 0.07
...ETC

THANKS

--

Dave Peterson