Index & Match

[Guest]

Hi please help me in solving the following issue
e.g i have the follwoing date in Excel Sheet

A B C D E
1 Jon 123
2 Suso 456
3 Mary 789
4 Jon 123
3 Suso 456
5 Mary 789

IF Jon (A1) and 123(B1) are locating in (A1:A5 & C1:C5) Then Copy A1 & B1 in
D1& E1 and copy A4 & C4 in D4&E4

 

[Guest]

One way ..

Getting results for the Top Half:
Put in D1, array-enter the formula by pressing CTRL+SHIFT+ENTER:
=IF(ISNUMBER(MATCH($A1&$B1,$A$4:$A$6&$C$4:$C$6,0)),A1,"")
Copy D1 to E1, fill down to E3. This returns the results for the top half.

Getting results for the Bottom Half:
Put in D4, array-enter the formula by pressing CTRL+SHIFT+ENTER:
=IF(ISNUMBER(MATCH($A4&$C4,$A$1:$A$3&$B$1:$B$3,0)),A4,"")

Put in E4,array-enter the formula by pressing CTRL+SHIFT+ENTER:
=IF(ISNUMBER(MATCH($A4&$C4,$A$1:$A$3&$B$1:$B$3,0)),C4,"")
Select D4:E4, fill down to E6. This returns the results for the bottom half.