Filter a range to get a pivot like result

Question

I want to filter a range of attendance in Google Sheet where the result will show the only the dates where the employee did Overtime in one column and the number of OT hours of the employee on the next column based on the value of the search box or a specific cell. [enter image description here] (https://i.stack.imgur.com/v6Q7u.png)text

https://docs.google.com/spreadsheets/d/1g4ZgmzDdWYFeWWt37ETRlXR9E4CAGM5Bm4-lnpWe2Ys/edit?usp=sharing

I tried filter functions and query functions but I don't have extensive understanding about these functions that's why I cannot get the result that I want. I'm not even sure if it's the correct function that I need to use on this problem. I hope you can help me on this and I'm looking forward to learn new things from you guys. Cheers!

Answer 1

You may try:

=let(Σ,transpose(filter(filter(D1:7,D2:2="OT"),(row(B1:B7)=1)+(B1:B7=C10))),
       filter(Σ,index(Σ,,2)<>""))