| 0 | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Q1 | Q2 | Q3 | Q4 | Product A | ||
| 2 | Product A | 500 | 900 | 100 | |||
| 3 | Product B | 600 | 400 | 500 | |||
| 4 | Product C | 100 | 350 | 785 | 900 | ||
| 5 | Product D | 230 | 600 | 210 | 690 | 100 | |
| 6 | Product E | 310 | 900 | 480 | 125 | ||
| 7 |
In Range G3:G6 I want to transpose the values based on the row criteria entered in Cell G1.
(Note: Products listed in Column A are always unique.)
In this example the Row B2:E2 for Product A is transposed an displayed in Range G3:G6.
So far I have been able to develop this formula:
=LET(
a,TRANSPOSE(B2:E2),
IFERROR(INDEX(FILTER(a,a<>0),SEQUENCE(4,,1)),""))
This formula works.
However, I have no clue how to make the Range B2:E2 in this formula flexible so it changes based on the input in Cell G1.
Do you have any idea how to modify it to make it work?
Another solution where you pass the whole data range to the data variable:
=LET(data,A2:E6,
Product,G1,
TOCOL(FILTER(DROP(data,,1),CHOOSECOLS(data,1)=Product,"no result"),1))