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

Closed
Report
-
 RayH -
Hello,
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:

=IFERROR(INDEX(Sheet2!$B$2:$B$6,MATCH($A2&$B2&$C2,Sheet2!$A$2:$A$6&Sheet2!$C$2:$C$6&Sheet2!$D$2:$D$6,0)),"")


To get the Address, use:

=IFERROR(INDEX(Sheet2!$E$2:$E$6,MATCH($A2&$B2&$C2,Sheet2!$A$2:$A$6&Sheet2!$C$2:$C$6&Sheet2!$D$2:$D$6,0)),"")


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