# Array / Lookup / lost the plot

Posted by George on November 14, 2001 5:59 AM

I am trying to pull information from one sheet to another. I need the address(column A) to be the same, the year (column C) to match and to get the relevent value for that address in that year to be pulled through from column G.

This does not work...
{=IF(AND(\$A\$4=Sheet1!\$A\$4:\$A\$4000,C4=Sheet1!\$C\$4:\$C\$4000),Sheet1!\$G\$4:\$G\$4000,"*")}

Any help appreciated.

George

Posted by IML on November 14, 2001 7:50 AM

If you are going after a number, you could try this formula
=SUMPRODUCT((Sheet1!\$A\$4:\$A\$4000=A4)*(Sheet1!\$B\$4:\$B\$4000=B4)*(Sheet1!\$G\$4:\$G\$4000))

If you are going after text, you could add a column C on sheet with this formula
=A4&"-"&B4 and copy it down.
You can then us this formula to retrieve.
=VLOOKUP(A4&"-"&B4,Sheet1!\$C\$4:\$H\$4000,6,0)

good luck

Posted by George on November 14, 2001 9:13 AM

Not Sumproduct, but thanks.

The value in Column G I am trying to pull through is a number, but i want just this number if the conditions previously mentioned are correct.

George

Posted by Aladin Akyurek on November 14, 2001 9:45 AM

Re: Not Sumproduct, but thanks.

I'm guessing that you're looking for (after Ian/IML):

=IF(SUMPRODUCT(ISNUMBER(MATCH(A4&"-"&C4,Sheet1!\$A\$4:\$A\$4000 &"-"& Sheet1!\$C\$4:\$C\$4000,0))+0),INDEX(Sheet1!\$G\$4:\$G\$4000,SUMPRODUCT(ISNUMBER(MATCH(A4&"-"&C4,Sheet1!\$A\$4:\$A\$4000 &"-"& Sheet1!\$C\$4:\$C\$4000,0))),"*")

Posted by George on November 15, 2001 2:51 AM

More Detail

You just completely lost me, but it sounds close.

Example
Sheet 1
A B C D G
2001/02 500
2002/03 520

2001/02 370
2002/03 210

2001/02 611
2002/03 650

Sheet 2
(The Sheet I am puting the formula in)

A B C D G
2001/02
2002/03

2001/02
2002/03

2001/02
2002/03

The address names on both sheets are identical, but not in the same order on both sheets. Both sheets have the same layout.

thanks
George

Posted by George on November 15, 2001 4:49 AM

This may make more sense

Sheet1
2001/02 500
2002/03 520

2001/02 370
2002/03 210

2001/02 611
2002/03 650

Sheet 2
2001/02
2002/03

2001/02
2002/03

2001/02
2002/03

I want to be able to pull through the value from sheet 1 to sheet 2 for the appropriate address in the relevent year. eg if Address3 in year 2001/02 is 611, i want 611 to show on sheet 2 for Address3 in year 2001/02.

All help appreciated
George

Posted by Aladin Akyurek on November 15, 2001 4:39 PM

Re: This may make more sense

George --

The organization of your data makes it not easy to come up with a solution without performance headache.

I'll use your sample data to show a method for how to obtain the desired results.

A1:C11 in Sheet1 houses the following sample data:

0,"2001/02",500;
0,"2002/03",520;
0,0,0;
0,"2001/02",370;
0,"2002/03",210;
0,0,0;
0,"2001/02",611;
0,"2002/03",650}

and A1:B13 in Sheet2 houses the following sample data:

{0,0;
0,"2001/02";
0,"2002/03";
0,0;
0,"2001/02";
0,"2002/03";
0,0;
0,0;
0,"2001/02";
0,"2002/03"}

Zeroes stand for blank cells. Note that I have an empty row just before the data, that is, cells A1 and B1 are blank.

You want retrieve values from column C in Sheet1 that correspond to each pair (such as "address1" and "2000/01") thai is in Sheet2.

In Sheet2,

in C2 enter: =IF(LEN(A2),0,C1+1) [ copy down as far as needed ]

To array-enter a formula, hit CONTROL+SHIFT+ENTER at the same time, not just ENTER.

After array-entering the last formula, copy it down as far as needed.

PS. If you'd like to have a copy of the workbook showing the above system of formulas, just drop me a line.

========

Posted by George on November 16, 2001 7:38 AM

IT WORKS

Thanks to all who had a look

BIG THANK YOU to reident genius "Aladin Akyurek"

Regards
George