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

Question

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

RayH · Answer

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.

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

1 response

Similar discussions

Subscribe To Our Newsletter!