CODEWITHSUNDEEP
excelexcel-formula
Josh Kern
Josh Kern

Reputation: 93

Excel - If then index match if

I have columns A, B, and C. Column D will be the new column that adds the spouse's name if found. The common information between the contacts is the address, so here's the formula I have so far:

=IF(B3="Spouse", IF(INDEX(B:B, MATCH(C3,C:C, 0))="Primary", INDEX(A:A, MATCH(C3,C:C, 0)), ""), "No Spouse")

The issue seems to be with MATCH(C3,C:C, 0). The C3 cell is finding itself in the C:C range.

   A                B              C                  D
Name             Relationship    Address             Spouse
Joe Smith        Primary         432 Park Dr         No Spouse
Michael Turner   Primary         2298 Thompson Rd    No spouse
Sarah Smith      Spouse          432 Park Dr         Joe Smith
Hannah Smith     Child           432 Park Dr         No Spouse

Upvotes: 0

Views: 1071

Answers (2)

Scott Craner
Scott Craner

Reputation: 152450

use this formula:

=IF(B2="Spouse","",IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($C$2:$C$4)/(($C$2:$C$4=C2)*($A$2:$A$4<>A2)),1)),"No Spouse"))

enter image description here

As per your new data:

=IF(B2="Spouse",IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($C$2:$C$5)/(($C$2:$C$5=C2)*($A$2:$A$5<>A2)*($B$2:$B$5 = "Primary")),1)),"No Spouse"),"No Spouse")

enter image description here

Upvotes: 2

tigeravatar
tigeravatar

Reputation: 26640

Alternate formula for backwards compatibility (in cell D2 and copied down):

=IF(OR(COUNTIF(C:C,C2)=1,B2="Child"),"No Spouse",INDEX($A$2:$A$4,MATCH(1,INDEX(($C$2:$C$4=C2)*($A$2:$A$4<>A2),),0)))

Upvotes: 0

Related Questions