This is solution for Microsoft 365:
[Sheet1!D2 and fill down]=TOROW(FILTER(Sheet2!$H$4:$H$9;Sheet2!$B$4:$B$9=C2))
Assuming the first picture is Sheet1, the second - Sheet2.