=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