How can I filter data from two work sheets by matching values

 RayH -
I have two Excel sheets. I want to copy name and address on D and E columns of sheet 1 from sheet 2 if both sex and age match.The first one have three columns, case_no,age and sex.The second sheet contains no,age, sex, Address and name ,sheet 2 may contain duplicate values . How can I do this using Excel 2007.
Sheet 1
case_no age  sex

101     23   F

102     18   F

103     45   M

208     64   F 

  209     19 M 

Sheet 2
 no     name  age   sex address?

101     Abe   56     M   dilla

 209     zedo  19     M   bonga

 206     rute  18     F   saris

70      hana  67     M   paissa

102     Feve  45     F   masr

Thank you.
with regards

1 reply

To get the Name, use:


To get the Address, use:


Important: Each formula MUST be entered by Ctrl-Shift-Enter as this is an Array Formula.