• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Matching with each other

AVK

Active Member
Hello Friends Firstly Wish you a very happy new.
i am little bit confused. I have use excel2007.
I have file with three sheet.
Sheet1 : Name1 / Name2 / Address / City / Country (Column A/B/C/D/E)
Sheet2 : Name / Depend / Connection (Column A/B/C)
In sheet2 i merged Name with sheet1 Name1&" "&Name2
Sheet3 : Name1 / Name2 / Address / City / Country / Wife / Son1 / Son2 (Column A/B/C/D/E/F/G/H)
In Sheet3 : I required result in column "F" / "G" / "H" which is in Sheet2 data Who of Depend (Dependend Name) column "B" but match with column "C" connection (Wife /Son).
Required result shown in attach file which is highlighted in yellow.
 

Attachments

  • Matching_Relation.xlsx
    8.7 KB · Views: 7
Try,

In F2, copy across and down :

=IFERROR(INDEX(Sheet2!$B$2:$B$5,AGGREGATE(15,6,(ROW(Sheet2!$A$2:$A$5)-ROW(Sheet2!$A$2)+1)/($A2&" "&$B2=Sheet2!$A$2:$A$5)/(LEFT(Sheet2!$C$2:$C$5,3)=LEFT(F$1,3)),COUNTIF($F$1:F$1,LEFT(F$1,3)&"*"))),"")

Regards
Bosco
 

Attachments

  • Matching_Relation.xlsx
    11 KB · Views: 7
Hello Friends Firstly Wish you a very happy new.
i am little bit confused. I have use excel2007.
I have file with three sheet.
Sheet1 : Name1 / Name2 / Address / City / Country (Column A/B/C/D/E)
Sheet2 : Name / Depend / Connection (Column A/B/C)
In sheet2 i merged Name with sheet1 Name1&" "&Name2
Sheet3 : Name1 / Name2 / Address / City / Country / Wife / Son1 / Son2 (Column A/B/C/D/E/F/G/H)
In Sheet3 : I required result in column "F" / "G" / "H" which is in Sheet2 data Who of Depend (Dependend Name) column "B" but match with column "C" connection (Wife /Son).
Required result shown in attach file which is highlighted in yellow.
Hi,

Another way of doing it (using array formula)... please refer to attachment

Hope it helps
 

Attachments

  • Matching_Relation.xlsx
    10.1 KB · Views: 8
Back
Top