Index Match Function Issues

[JimmyG.]

I have a sheet with a list of employees and some tests they have taken. Each employee is on either Team A, B or C. The data shows Employee Name, Team Name, Test Number and Pass or Fail.

I want a summary sheet that I can use a drop down and choose Team Name. I then want the information to show the employee, which test they took and if it was a pass.

I tried using Index Match which did work but filled in more employees than there were so duplicated things, because its duplicating things I cant work out the rest of the formulas.

I would like it to only show the employee on the summary sheet when they have passed the test - this may mean duplicate employee entries but that is fine as it would be for the different tests - unless it can be done is such a way there would be only 1 employee and the Pass section will fill in as more tests are completed and passed

In the attached (basic form of the data) Sheet 1 holds the data and Sheet 2 is what the summary would be.

Sheet 3 is manually done to show the outcome I can looking for - don't pay attention to the names as these are just randomly made up for the test data.

If anyone can help, and explain how to do this that would be great. Thank you

Test.xlsx

[Glenn Kennedy]

Was it intentional that Team A's John did Test 1 twice and got different results (one pass and one fail)?

[JimmyG.]

Yes, they can keep doing the test until they pass - the summary then allows you to see who still needs to pass which tests

[LokeshKumar]

Hi,

I am not sure but what I have tried I am gonna attach here.....Test.xlsx

If your question is resolved, mark it SOLVED using the thread tools. Click on the star if you think some-1 helped you.

[JimmyG.]

Hi, that works great - took me a while to work out I needed named ranges but it does what I need it to - thank you.

One change I've been asked to try and do is would it be possible in the summary part to show the attempt number that they passed on. So rather than it being pass or no data could it be 1 or 2 or 3 etc once the pass has been achieved.
In the data there is a column that states attempt number so I guess it would be a case of when it says pass excel would look in that column and display the number.

[LokeshKumar]

Yes counta function will do the job.
I am on my iPhone so when I will get the time the. I will try..