Help with a nested IF formula?

[kordite23]

Hi, I am running a test for parentage, comparing dna markers from possible parents. I am looking for a formula to be able to compare the numbers of the offspring against the parents, with the goal of getting an output of which father the offspring came from. Example for Offspring1 the output would be Father 2. If anyone would be able to help me it would be greatly appreciated.



[TABLE="width: 156"]
<tbody>[TR]
[TD]Mother[/TD]
[TD]241, 255[/TD]
[/TR]
[TR]
[TD]Father 1[/TD]
[TD]256, 259[/TD]
[/TR]
[TR]
[TD]Father 2[/TD]
[TD]238, 261[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Offspring1[/TD]
[TD]241, 261[/TD]
[/TR]
[TR]
[TD]Offspring2[/TD]
[TD]255, 256[/TD]
[/TR]
[TR]
[TD]Offspring3[/TD]
[TD]238, 255[/TD]
[/TR]
</tbody>[/TABLE]

 

[Dim Me as xlNoob]

So one of the numbers has to match the mother and the other number will match one of the fathers?

 

[kordite23]

Yes that is exactly it!

 

[Dim Me as xlNoob]

Sorry one more question before I start on it, does it matter which number matches? As in can the left numbers only match the other left numbers? or can they match the number on the right as well. If so how do you determine which match gets preference?

 

[kordite23]

Either number can match left or right, just has to be one from the mother and one from one of the fathers. There isn't any preference as far as how they match.

 

[Dim Me as xlNoob]

Hmm I have half a solution, I'm sure someone else here will be able to help out.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Mother[/TD]
[TD]241[/TD]
[TD]255[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Father 1[/TD]
[TD]256[/TD]
[TD]259
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Father 2[/TD]
[TD]238[/TD]
[TD]261[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Offspring 1[/TD]
[TD]241[/TD]
[TD]261[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Offspring 2[/TD]
[TD]255[/TD]
[TD]256[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Offspring 3[/TD]
[TD]238[/TD]
[TD]255[/TD]
[/TR]
</tbody>[/TABLE]

With the data set out as above I came up with:

D4: =IF(MATCH(B4,$B$1:$C$1,0),IFERROR(INDEX($A$2:$A$3,MATCH(C4,$B$2:$B$3,0)),IFERROR(INDEX($A$2:$A$3,MATCH(C4,$C$2:$C$3,0)),"NO MATCH WITH FATHER")),IF(MATCH(C4,$B$1:$C$1,0),IFERROR(INDEX($A$2:$A$3,MATCH(B4,$B$2:$B$3,0)),IFERROR(INDEX($A$2:$A$3,MATCH(B4,$C$2:$C$3,0)),"NO MATCH WITH FATHER")),"NO MATCH WITH MOTHER"))

Which worked for offspring 1 and 2 but came up with #N/A on 3. I don't know why but I think it has something to do with the fact that the right hand number is the one which matches with the mother and my logic must be jumbled up in the formula. I thought I stepped through it correctly but must have gotten lost somewhere.

 

[Dim Me as xlNoob]

I've fixed up my formula and should work now:

=IF(NOT(ISERROR(MATCH(B6,$B$1:$C$1,0))),IFERROR(INDEX($A$2:$A$3,MATCH(C6,$B$2:$B$3,0)),IFERROR(INDEX($A$2:$A$3,MATCH(C6,$C$2:$C$3,0)),"NO MATCH WITH FATHER")),IF(NOT(ISERROR(MATCH(C6,$B$1:$C$1,0))),IFERROR(INDEX($A$2:$A$3,MATCH(B6,$B$2:$B$3,0)),IFERROR(INDEX($A$2:$A$3,MATCH(B6,$C$2:$C$3,0)),"NO MATCH WITH FATHER")),"NO MATCH WITH MOTHER"))

Let me know how it goes for you.

 

[kordite23]

Thanks you so much it works great!

 

[Dim Me as xlNoob]

Good to hear, thanks for the feedback.