Index array list based on multiple matching Values

Originally Posted by JohnTopley

Try

for each department sheet : assumes Data is organised as per your current DATA sheet)

in B8

=IF(ROWS($1:1)<=COUNTIF(Data!$A$1:$A$1000,$B$2),INDEX(Data!B$1:B$1000,MATCH($B$2,Data!$A$1:$A$1000,0)+ROWS($1:1)-1),"")

Copy down and across to D

if F8

=IF(ROWS($1:1)<=COUNTIF(Data!$A$1:$A$1000,$B$2),INDEX(Data!E$1:E$1000,MATCH($B$2,Data!$A$1:$A$1000,0)+ROWS($1:1)-1),"")

See "Dept_0" / "Dept_1" in attached.

It appears to be working perfectly! You're a life saver!