HI,

I have a dataset in one work sheet. The data, cells, can be lovated by
three identifiers. Each column has an identifier (month) and each row
have two identifier that has to combined. Region and Year.
Below is an example that shows the layout of the data.

* 1 2 3 4*
*101 2001* 1.81 2.27 5.03 1.6
*101 2002* 2.27 2.11 3.72 4.09
*101 20*03 2.26 3.24 3.23 2.65
*101 2004* 2.06 1.79 2.38 4.59
*101 2005* 4.01 2.37 3.65 5.16
*102 2001* 1.21 1.92 0.81 4.4
*102 2002* 0.57 1.33 1.85 3.3

How can write a formula/macro where I can put the three identifiers and
retrive the data/cell into a new work sheet?

Thanks in advance
Stefan


--
stefan7219
------------------------------------------------------------------------
stefan7219's Profile: http://www.excelforum.com/member.php...o&userid=27173
View this thread: http://www.excelforum.com/showthread...hreadid=466862

Try the following array formulas that need to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=INDEX(Sheet1!C2:F8,MATCH(1,(Sheet1!A2:A8=101)*(Sh eet1!B2:B8=2001),0),MAT
CH(3,Sheet1!C1:F1,0))

or

=INDEX(Sheet1!C2:F8,MATCH(1,(Sheet1!A2:A8=H2)*(She et1!B2:B8=I2),0),MATCH(
J2,Sheet1!C1:F1,0))

....where H2 contains the region of interest, such as 101, I2 contains
the year of interest, such as 2001, and J2 contains the month of
interest, such as 3.

Hope this helps!

In article ,
stefan7219
wrote:

HI,

I have a dataset in one work sheet. The data, cells, can be lovated by
three identifiers. Each column has an identifier (month) and each row
have two identifier that has to combined. Region and Year.
Below is an example that shows the layout of the data.

* 1 2 3 4*
*101 2001* 1.81 2.27 5.03 1.6
*101 2002* 2.27 2.11 3.72 4.09
*101 20*03 2.26 3.24 3.23 2.65
*101 2004* 2.06 1.79 2.38 4.59
*101 2005* 4.01 2.37 3.65 5.16
*102 2001* 1.21 1.92 0.81 4.4
*102 2002* 0.57 1.33 1.85 3.3

How can write a formula/macro where I can put the three identifiers and
retrive the data/cell into a new work sheet?

Thanks in advance
Stefan

Note that I've assumed that Sheet1 contains your table. Change the
sheet name accordingly.

In article ,
Domenic wrote:

Try the following array formulas that need to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=INDEX(Sheet1!C2:F8,MATCH(1,(Sheet1!A2:A8=101)*(Sh eet1!B2:B8=2001),0),MAT
CH(3,Sheet1!C1:F1,0))

or

=INDEX(Sheet1!C2:F8,MATCH(1,(Sheet1!A2:A8=H2)*(She et1!B2:B8=I2),0),MATCH(
J2,Sheet1!C1:F1,0))

...where H2 contains the region of interest, such as 101, I2 contains
the year of interest, such as 2001, and J2 contains the month of
interest, such as 3.

Hope this helps!

In article ,
stefan7219
wrote:

HI,

I have a dataset in one work sheet. The data, cells, can be lovated by
three identifiers. Each column has an identifier (month) and each row
have two identifier that has to combined. Region and Year.
Below is an example that shows the layout of the data.

* 1 2 3 4*
*101 2001* 1.81 2.27 5.03 1.6
*101 2002* 2.27 2.11 3.72 4.09
*101 20*03 2.26 3.24 3.23 2.65
*101 2004* 2.06 1.79 2.38 4.59
*101 2005* 4.01 2.37 3.65 5.16
*102 2001* 1.21 1.92 0.81 4.4
*102 2002* 0.57 1.33 1.85 3.3

How can write a formula/macro where I can put the three identifiers and
retrive the data/cell into a new work sheet?

Thanks in advance
Stefan

Thanks Domenic.

I must be doing something wrong, because all I get from the formula is
"N/A"

Did you actually try the formula and it worked?

Thanks
Stefan


--
stefan7219
------------------------------------------------------------------------
stefan7219's Profile: http://www.excelforum.com/member.php...o&userid=27173
View this thread: http://www.excelforum.com/showthread...hreadid=466862

Did you use ctrl-shift-enter to enter the formula?

Did you double check the values to verify that there is a match in the required
columns/rows?

stefan7219 wrote:

Thanks Domenic.

I must be doing something wrong, because all I get from the formula is
"N/A"

Did you actually try the formula and it worked?

Thanks
Stefan

--
stefan7219
------------------------------------------------------------------------
stefan7219's Profile: http://www.excelforum.com/member.php...o&userid=27173
View this thread: http://www.excelforum.com/showthread...hreadid=466862

--

Dave Peterson

I made sure I used ctrl+shift and Enter. It did not make a difference.

I've trying to disect the formula and see where I might be going
wrong.

At this time I can't replicate the middle part :
"MATCH(1,(Sheet1!A2:A8=101)*(Sh eet1!B2:B8=2001),0)"

So I'm thinking, something is wrong in here.

Anyway, if somebody have any ideas, please let me know.

// Stefan


--
stefan7219
------------------------------------------------------------------------
stefan7219's Profile: http://www.excelforum.com/member.php...o&userid=27173
View this thread: http://www.excelforum.com/showthread...hreadid=466862