Hi all,

Can someone help me with this problem? I like to use MATCH & INDEX formula to list all matches.

Table is like this..

A1: Staff No. B1: Department C1: Name D1: Sales E1: 1
A2: 1 B2: Sales C2: Amy
A3: 2 B3: Finance C3: Jill
A4: 3 B4: Sales C4: Tim
A5: 4 B5: Finance C5: Sam
A6: 5 B6: Sales C6: Paul

I want to pick all Staff No. of Sales Department and put them in column E.

At cell E1, I entered this formula =INDEX($A$2:$A$6,MATCH($D$1,$B$2:$B$6,0))

but how to list the rest of Staff No. in cell E2, E3 and so on in column E?

Thanks.