Fetching Data from a form sheet and populate in different sheets as per values in Microsoft Forms without #VALUE! error

I have a requirement in which I have a Microsoft Form, when a user fills it, it captures the data in a sheet. Now the form consists of branching which means based on a certain value the fields change. As shown in the picture below, based on the Type A or Type B the form gets populated.

What I want to do is that I want to populate different information based on "Type A", "Type B" or "Type C" in the sheets below.

What I have attempted is that in the Sheet Type A:

=IF(ISBLANK(OfficeForms.Table[@[Content Type]]),"",IF(OfficeForms.Table[@[Content Type]]="Type A",OfficeForms.Table[@[Content Type]],""))

When I use the above code, what happens is that it leaves a blank row and when I pull the formula down on other rows it gives #VALUE! error.

Is it possible to apply the formula on the whole of the column without having that VALUE error displayed. I understand it is a form, and when the form gets filled the #VALUE! starts to disappear, but I don't want it to be displayed like that.

I have to do the same thing in with other Content Types in different sheets. Is there a better way to do this? Any recommendations would be appreciated.

Thanks.

Solution

If you have Excel for Microsoft 365, you could try using FILTER:

use a variable for Content Type and use it for column names
"Type " & type & " [info field 1]"
since we will be reusing the formula in multiple sheets, we will create function with type as the input
first, for testing, enter this in Type A sheet, where you want to populate the results:

=LAMBDA(type,
    LET(
        col_headers, HSTACK(
            "Id",
            "Type " & type & " [info field 1]",
            "Type " & type & " [info field 2]"
        ),
        cols, CHOOSECOLS(
            OfficeForms.Table,
            XMATCH(col_headers, OfficeForms.Table[#Headers])
        ),
        VSTACK(
            col_headers,
            FILTER(cols, OfficeForms.Table[Content type] = "Type " & type)
        )
    )
)("A")

We will give the formula a name.

copy the formula, leaving out ("A") (the parameter)
then in Name Manager > New add a named formula with getResults as the name
paste the copied formula in Refers to:
now we can use it with different types:
=getResults("A")