Extract values and the use index to transpose values across columns to rows

Solution I: Looking for formula to first extract values if conditions are met (see Outcome Sheet for desired solution). For example, if West is selected in B2, values associated with West in Product Sheet are extracted into A6:N10.

Solution II: Use extracted values in A6:N10 to then transpose values across columns to rows. Example: if West is selected in B2 (Outcome Sheet), formula should look into D2:G3 (Outcome Sheet), extract Fruit List for West into B13:D13 and values associated with these fruit list into B14:D25.

Sample file attached.

Thanks

In A6:N10 "Peach" is included. It is blank for 3 columns.

"Peach" is not listed in the criteria (?) list E3:G3.

In the transposed list "Peach" is not included in the header. "Plum" is included and is blank for 3 rows.

I am not entirely clear what / what not to include.

"Plum" is included because it is a required criteria for the transpose and peach is not. Only 3 criteria (Papaya, Plum, Pineapple) are required for the transpose and not all criteria.

It still has 3 blank rows in the transposed table. Is this a typo?

To extract the source data by Region array enter this formula in A6 copy down and across until you get blanks. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

To get the headers for the transposed table enter this non array formula in B13 and copy across.

Formula:

Please Login or Register  to view this content.

To transpose the dates this non array formula in A14 and copied down.

Formula:

Please Login or Register  to view this content.

To transpose the product amounts this non array formula in B14 copied down and across

Formula:

Please Login or Register  to view this content.