Match - Array Problem

[Guest]

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

 

[Peo Sjoblom]

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

array entered with ctrl + shift & enter

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

 

[Guest]

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

 

[Peo Sjoblom]

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