Here is a sample of what i need to do
There is a table above which i update regularly who uses which product. but i want to make a list like the one below the table, with which user used which products on a specific date. Like using indirect or array type formula, is there anyway to get horizontal data into vertical rows automatically.
CodePudding user response：
No problem! ^_^
The Formula
=TRANSPOSE(FILTER($B$1:$F$1;FILTER($B$2:$F$4;$A$2:$A$4=$A$12)=D13))
Explanation for the references:
| Reference | Explanation |
|---|---|
| $B$1:$F$1 | The top row (Product names) |
| $B$2:$F$4 | The table data (excluding date and product name) |
| $A$2:$A$4 | The left-most column (Dates) |
| $A$12 | The specific date we want to look up |
| D13 | The specific Person we want to look up |
This formula is "self-contained", so if you put "Ashley" next to D14, you can copy this this formula underneath (to D15) and it will work for Ashley too!
Of course, feel free to move this formula around, when the table expands, you can adjust the references.
Have fun! ^_^