Array / Lookup / lost the plot

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

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

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))),"*")

If not, please elaborate.

Aladin

More Detail

You just completely lost me, but it sounds close.

Example
Sheet 1
A B C D G
address1 2000/01 456
2001/02 500
2002/03 520

address2 2000/01 256
2001/02 370
2002/03 210

address3 2000/01 555
2001/02 611
2002/03 650

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

A B C D G
address1 2000/01
2001/02
2002/03

address3 2000/01
2001/02
2002/03

address7 2000/01
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

This may make more sense

Sheet1
Address1 2000/01 456
2001/02 500
2002/03 520

Address2 2000/01 256
2001/02 370
2002/03 210

Address3 2000/01 555
2001/02 611
2002/03 650

Sheet 2
Address1 2000/01
2001/02
2002/03

Address3 2000/01
2001/02
2002/03

Address7 2000/01
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

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:

{"address1","2000/01",456;
0,"2001/02",500;
0,"2002/03",520;
0,0,0;
"address2","2000/01",256;
0,"2001/02",370;
0,"2002/03",210;
0,0,0;
"address3","2000/01",555;
0,"2001/02",611;
0,"2002/03",650}

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

{0,0;
"address1","2000/01";
0,"2001/02";
0,"2002/03";
0,0;
"address3","2000/01";
0,"2001/02";
0,"2002/03";
0,0;
0,0;
"address7","2000/01";
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 ]

in D2 array-enter: =IF(LEN(B2),IF(ISNUMBER(MATCH(INDIRECT(ADDRESS(MAX(($A$1:A2<>0)*ROW($A$1:A2)),1)),Sheet1!$A:$A,0)),INDEX(Sheet1!$C:$C,MATCH(INDIRECT(ADDRESS(MAX(($A$1:A2<>0)*ROW($A$1:A2)),1)),Sheet1!$A:$A,0)+C2),"Not Found"),"")

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.

Aladin

========

IT WORKS

Thanks to all who had a look

BIG THANK YOU to reident genius "Aladin Akyurek"

Regards
George