I'm new to excel and am having trouble finding a function that will allow me to do this:

I am trying to get the members name to auto populate in sheet2 if their TSC equals the specified value(For instance "K" or "M". So if the members TSC is changed to K in sheet1 then in sheet2 their name will automatically appear under the Training Status Code section for that letter.

I've been trying to use the IF function to do this. This is what I have:

=IF(Sheet1!F:F = "M", Sheet1!A:A,"")

In the attached screenshot you can see that for training status code M Garcia has populated but no other name has, I don't understand why its doing that. I also specified the cells so F5:F10 and A5:A10 but it didn't seem to make a difference.

Does anyone know how to solve this or a better way to approach this?

Sheet1

Sheet2

Also, I'm using excel 2016!

CodePudding user response：

=IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,1/(Sheet1!F:F="M")*ROW(Sheet1!F:F),ROW(1:1))),"")

This formula indexes column A of Sheet1 where the smallest row number of column F in Sheet1 equals M. If you drag it down ROW will behave as a counter for the Nth smallest match.

You need to enter this array-formula using ctrl shift enter