trouble bringing in data from multiple google sheets into a master sheet

Question

I have a "Raw Data" worksheet I want to extract columns E, U, D, C, I in that order.

I then want to grab all statuses associated with key [landowner](column D in "Raw Data") in "Comments" worksheet.

I then want to grab MAX [status] value from "Statuses" worksheet based on landowner key.

I then want to grab columns F, W, G, K, A, B from "Raw Data". I highlighted in the picture below the landowners who have data in "Statuses" and Comments" worksheet I want brought in.

Here is the desired output in the yellow colored sheet in my workbook:

I tried using this formula to replicate that table above, but I keep getting parse errors. Any clue what's going on?

=LET(
    comment_id, Comments!A2:A,
    comment_comment, Comments!B2:B,
    comment_date, Comments!C2:C,
    table, 
    map(
        comment_id, comment_comment, comment_date, 
        lambda(c_id, c_c, c_d, 
            IF(
                c_id,F13 
                HSTACK(c_id, TEXT(c_d, "m/d/yy : ") & c_c)
            )
        )
    ),
    substation, 'Raw Data'!E2:E, 
    parcel_id, 'Raw Data'!U2:U,
    landowner, 'Raw Data'!D2:D,
    address, 'Raw Data'!C2:C,
    acres, 'Raw Data'!I2:I,
    status, 'Status'!A2:B, 
    max_status, 
    map(
        landowner,
        lambda(landowner_name,
            IF(
                landowner_name<>"",
                MAX(FILTER(status[Column2], status[Column1]=landowner_name)),
                ""
            )
        )
    ),
    col_f, 'Raw Data'!F2:F,
    col_w, 'Raw Data'!W2:W,
    col_g, 'Raw Data'!G2:G,
    col_k, 'Raw Data'!K2:K,
    col_a, 'Raw Data'!A2:A,
    col_b, 'Raw Data'!B2:B,
    VSTACK(
        {"Substation", "Parcel ID", "Landowner", "Address", "Acres", "Comments", "Max Status", "Distance from Hub", "Agent", "Utility", "County", "Latitude", "Longitude"},
        map(
            substation, parcel_id, landowner, address, acres,
            lambda(sub, parcel, owner, addr, acr,
                HSTACK(
                    sub, parcel, owner, addr,
                    IFERROR(
                        TEXTJOIN(
                            CHAR(10),
                            TRUE,
                            FILTER(
                                table,
                                table[[Column1]:[Column1]] = parcel
                            )
                        ),
                        ""
                    ),
                    FILTER(max_status, landowner=owner),
                    INDEX(col_f, ROWS(HSTACK(substation))),
                    INDEX(col_w, ROWS(HSTACK(substation))),
                    INDEX(col_g, ROWS(HSTACK(substation))),
                    INDEX(col_k, ROWS(HSTACK(substation))),
                    INDEX(col_a, ROWS(HSTACK(substation))),
                    INDEX(col_b, ROWS(HSTACK(substation)))
                )
            )
        )
    )
)

The attempted formula is in cell a18 in last (yellow) worksheet. Can anyone assist?

TEST SHEET

EDIT: I just had a question regarding the comments CONCAT. I noticed that there was random spacing. is this fixable?

Answer 1

Here's one approach you may test out:

=filter(hstack(choosecols('Raw Data'!A2:Z,5,21,4,3,9),
        map('Raw Data'!D2:D,lambda(Σ,if(Σ="",,textjoin(char(10),1,ifna(filter(TEXT(Comments!C:C,"m/d/yy : ")&Comments!B:B,Comments!A:A=Σ)))))),
        xlookup('Raw Data'!D2:D,Statuses!A:A,Statuses!B:B,,,-1),
        choosecols('Raw Data'!A2:Z,6,23,7,11,1,2)),
  'Raw Data'!A2:A<>"")