Locate and retrivie data in table/matrix

S

stefan7219

HI,

I have a dataset in one work sheet. The data, cells, can be lovated b
three identifiers. Each column has an identifier (month) and each ro
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 an
retrive the data/cell into a new work sheet?

Thanks in advance
Stefa
 
D

Domenic

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)*(Sheet1!B2:B8=2001),0),MAT
CH(3,Sheet1!C1:F1,0))

or

=INDEX(Sheet1!C2:F8,MATCH(1,(Sheet1!A2:A8=H2)*(Sheet1!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!
 
D

Domenic

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

stefan7219

Thanks Domenic.

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

Did you actually try the formula and it worked?

Thanks
Stefa
 
D

Dave Peterson

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?
 
S

stefan7219

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
 
D

Dave Peterson

Your original data had some funny asterisks throughout.

I think Domenic thought they were typos in the post--but if that's the way your
data actually looked, then this formula still worked for me:

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

Still array entered.

I still think it's a difference in data--not the formula.
 
D

Domenic

Dave Peterson said:
Your original data had some funny asterisks throughout.
I think Domenic thought they were typos in the post...
Click to expand...

Yep, that's right. I thought they were either typos or some sort of
demarcation. As it turns out, looking at the post from within the
originating forum, it denotes bolding.
I still think it's a difference in data--not the formula.
Click to expand...

Yeah, I think so... :)
 
D

Domenic

Stefan,

If you'd like, I can email you a sample file. If so, you can send me
your email address at (e-mail address removed)
 
D

Dave Peterson

Very industrious!

I have a difficult enough time just finding these here newsgroups!
 
S

stefan7219

Thanks Dave,
obviously I'm doing something wrong, so at this point I'm thinking i
might be the entry of the formula.
I write the formula, actually copy&paste, and then hit Ctrl+Shif
Enter.

Is that the correct way of doing it?

// Stefan

PS: What is your output from the formula as written with the dat
provided
 
G

GeorgeF

Stefan,
In reviewing the formula that Domenic gave on the first response,
I noticed that his third expression of "Sheet1!" is shown as "S heet1!"
with a blank space after "S". Your message indicates that you copied
exactly. Is that the potential typo that you may be looking for?
GeorgeF
 
S

stefan7219

I saw that also and I corrected for it.

I still can't get to work.

George, is the formula working for you?

// Stefa
 
D

Domenic

Stefan,

To make sure that we're in the same wave length, here is how I
understood that your data is laid out...

A2:A8 contains the 'Region', such as 101

B2:B8 contains the 'Year', such as 2001

C1: 1

D1: 2

E1: 3

F1: 4

C2:F8 contains your data

Then, the following formula...

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

....confirmed with CONTROL+SHIFT+ENTER, will return 0.81. Does this help?
 
D

Dave Peterson

An unfortunate line break:

=INDEX(Sheet1!C2:F8,MATCH(1,(Sheet1!A2:A8=102)*(Sheet1!B2:B8=2001),0),
MATCH(3,Sheet1!C1:F1,0))

....confirmed with CONTROL+SHIFT+ENTER, will return 0.81. Does this help?

(copy and paste directly into the formula bar--then hit ctrl-shift-enter.)
 
G

GeorgeF

Stefan,
For the amount of time I've spent on this, I don't see th
solution. I initially reproduced the "5.03", then I change th
equation and have gone from N/A to 0. Where did you place the equatio
on Sheet 2? In C2? Sorry, I've run out of time for the weekend.
Georege
 
S

stefan7219

First Thanks for trying to help me.

I redid every thing from scratch. Ensured that your data was the sam
as mine, and it was.

I copied and pasted, in sheet 2 and sheet 1, in the formula in th
formula bar.

With the formula as written, I got a little box come up asking me t
update the values: eet1.

I'm asuming that comes from the third "Sh eet1" and the space betwee
the h & e, in the formula.
I took out the space and tried again. That did not work either.

I keep getting the"#N/A" error in cell.
If I click on the exclamation point next to the cell to evaluate th
error it looks like something is wrong in the middle of the formula
the second "Sheet1" statement.
It is underlined and indicates something is wrong.

Could I be missing some kind of Add-In?

I'm not giving up, if it works for you the formula must work for me.
just need to figure out how I have srewed it up so far.

Any ideas?

// Stefa
 
D

Dave Peterson

Maybe all the problems are caused by seeing the suggestions via the excelforum.

It seems to be adding extra characters where there are none. (Google can do
this, too.)

Maybe connecting to the newsgroups directly would make it easier to copy|paste.

Saved from a previous post:

If you have Outlook Express installed, try clicking on these links (or copy and
paste into MSIE).

news://msnews.microsoft.com/microsoft.public.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions
news://msnews.microsoft.com/microsoft.public.excel.newusers
news://msnews.microsoft.com/microsoft.public.excel.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsoft.public.word.docmanagement
news://msnews.microsoft.com/microsoft.public.word.word97vba
news://msnews.microsoft.com/microsoft.public.word.newusers
news://msnews.microsoft.com/microsoft.public.word.pagelayout
news://msnews.microsoft.com/microsoft.public.word.vba.addins
news://msnews.microsoft.com/microsoft.public.word.vba.beginners
news://msnews.microsoft.com/microsoft.public.word.vba.customization
news://msnews.microsoft.com/microsoft.public.word.vba.general
news://msnews.microsoft.com/microsoft.public.word.vba.userforms
news://msnews.microsoft.com/microsoft.public.word.word6-7macros

(You can always connect to more later)

Here are some links that explain it better:

Chip Pearson has some notes written by Leonard Meads at:
http://www.cpearson.com/excel/DirectConnect.htm

David McRitchie's notes at:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/oe6.htm
http://www.mvps.org/dmcritchie/excel/oe6nws01.htm

Tushar Mehta's notes at:
http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.htm

And if you're looking for old posts:

Or you can use google to search for stuff you've posted (and find the replies,
too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm
 
Top