Help with reference formulas for a family tree builder.

MelanieBeth  Sure, no problem.  Here is the formula:

=TRANSPOSE(XLOOKUP($C$14*2^((COLUMN()-3)/2)+COUNTIF(B$1:B15,"=Birth"),gedcomdata!$A:$A,gedcomdata!$E:$G,"N/A",0))

TRANSPOSE just converts it to a vertical array instead of horizontal

XLOOKUP finds the information based on:

    Lookup_Value =  $C$14*2^((COLUMN()-3)/2)+COUNTIF(B$1:B15,"=Birth")

               this calculation is based on the COLUMN() of "this" cell the formula is in and based on the layout you are using, by subtracting 3 and dividing by 2 it gives the power to which 2 needs to be raised for that generation and multiplies that by the initial Key Value (located at $C$14).

               then I add the count of values being done above by counting how many times you have the Label "Birth" in the rows starting at B$1 up until this row (B15 in this example) to increment within that generation.

    Lookup_Array = gedcomdata!$A:$A

                simply the column to search

    Lookup_Return = gedcomdata!$E:$G

                 returns the values from column E through G of the row corresponding to where the lookup was found.  So to make this NOT an array you just need to use $E:$E for NAME, $F:$F for BIRTH, or $G:$G for DEATH (and you don't need the TRANSPOSE since it is only 1 value)

     If_not_found = "N/A" = is just what it will show if the lookup value can't be found

     Search_type = 0 = just says to only find exact matches

So here is an example of the NAME without it being the Array:

=XLOOKUP($C$14*2^((COLUMN()-3)/2)+COUNTIF(B$1:B15,"=Birth"),gedcomdata!$A:$A,gedcomdata!$E:$E,"N/A",0)

as you can see it doesn't change much which is part of why making it an array is so nice.

I hope that helps you understand the formula better.  Again, this is based on you specific layout and may need to be changed if you change your layout (e.g. if you insert additional columns between generations).  A more flexible layout/formula would be to include an "index" for each 'block' in a cell next to or above that 'block' so instead of that complicated formula I put together you just multiply by that 'index' value.  So parents would be index values of 2 and 3, next gen has 4, 5, 6, 7 and so on.  But since your 4th gen or didn't have any spaces I would have had to change your layout to do that.  Alternatively, you could just put a "parental generation" value at the top of the column and use that partially simplify the formula.  e.g. P-Gen 1, P-Gen 2 and then just use 2^P-Gen+count("BIRTH")

For your enjoyment I made this tweak in the attached file and here is the new slightly simpler equation:

=TRANSPOSE(XLOOKUP($C$14*2^C$1+COUNTIF(B$1:B15,"=Birth"),gedcomdata!$A:$A,gedcomdata!$E:$G,"N/A",0))