I have a workbook with 2 sheets Data and Summary

Col A of Data contains Names and the same names appear more than once.

Col U of Data contains Numbers and the same numbers may appear motre than
once but not against the same name in Col A

In Col A of Summary I have a list of numbers and in cell B1 I have a Name.

In Col B of Summary (from row 2 onwards) I want a formula which returns
whatever is in Data!V1:V5000 where Col A = Data!U1:U5000 and B1 =
Data!A1:A5000


I've tried all sorts of things the latest of which is:
=if(and(=IF(AND($A2=Data!$U$1:$U$5000,B$1=Data!$A$ 1:$A$5000),Data!$V$1:$V$5000,"") entered as an array but none of them work.

Please could someone give me a steer on what I'm doing wrong and how to put
it right.

Many thanks

=INDEX(Data!V1:V5000,MATCH(1,(Data!U1:U5000=A2)*(D ata!A1:A5000=B1),0))

array entered with ctrl + shift & enter

depending on what's in your workbook it be a bit slow

--
Regards,

Peo Sjoblom



"nospaminlich" wrote in message
...
I have a workbook with 2 sheets Data and Summary

Col A of Data contains Names and the same names appear more than once.

Col U of Data contains Numbers and the same numbers may appear motre than
once but not against the same name in Col A

In Col A of Summary I have a list of numbers and in cell B1 I have a Name.

In Col B of Summary (from row 2 onwards) I want a formula which returns
whatever is in Data!V1:V5000 where Col A = Data!U1:U5000 and B1 =
Data!A1:A5000


I've tried all sorts of things the latest of which is:
=if(and(=IF(AND($A2=Data!$U$1:$U$5000,B$1=Data!$A$ 1:$A$5000),Data!$V$1:$V$5000,"")
entered as an array but none of them work.

Please could someone give me a steer on what I'm doing wrong and how to
put
it right.

Many thanks

Many thanks Peo. That works a treat although as you predicted it is a bit
slow as it's a busy workbook.

I assume it can't be done without an array formula in the same way
Sumproduct can be used as an alternative when calculating results?

Thanks again

Sumproduct is not that fast when it works as an array horse like in

sumproduct(--(range1=x),--(range2=y),range3)


Sumproduct could be used in this case if the values that you want to
retrieve in Data!V1:V5000 are numerical? If so use

=SUMPRODUCT(--(Data!U1:U5000=A2),--(Data!A1:A5000=B1),Data!V1:V5000)

which still probably would be slow but most likely faster than the
INDEX(MATCH combo

A much faster option but quite labour intensive to setup would be to use for
instance a hidden column (you would hide it when you are done with the
setup) and use a single formula per row, basically

=IF(AND(Data!U1=$A$2,Data!A1=$B$1),Data!V1,"")

copy down 5000 rows and then simply sum the whole column of help formulas.
That would be much faster than a single array formula


--
Regards,

Peo Sjoblom



"nospaminlich" wrote in message
...

Many thanks Peo. That works a treat although as you predicted it is a bit
slow as it's a busy workbook.

I assume it can't be done without an array formula in the same way
Sumproduct can be used as an alternative when calculating results?

Thanks again