Trying to create multiple Criteria

[pasqui83]

Hi all,

I have spent a week of spare time (probably around 3 days in total) trying to put together multiple criterias for a scorecard I am creating and keep getting stuck.

Scenario
I am creating a scorecard on 4 criteria:

1) Minimum Opportunites to be 100 or more
2) Type of Team they are in (inbound, outbound, email, temporary staff)
3) How many points they have totaled
4) Based on the points, what there names are.

The 4th criteria is fine as can do a vlookup on the points to match this correctly.

Example
I am creating a criteria which says, out of the top 5 people in the inbound department, show me the last highest position number. This drops down onto my spreadsheet and gives me the peoples names. If I change the top 5 to the top 3, it automatically shows me the 3 people

The formula I am using to show the last position of the TOP 5 is:

=IF(A21="","",LARGE('Total Score'!$W:$W,A21))

Where $W:$W is the total score numbers, and A21 is listing top 5.

I keep going round in circles. The more I read on this now, the more I just don't seem to be getting anywhere effectively.

Currently, I have a unique id cell in sheet 2, which I then point my vlookup, however I unable to tie in the opportunities.

I have tried the Index and match but seem to get lost on how to put it together (after looking at lots of different youtube videos). I did get 1 completed but when I put this into my sheet, it bought back results but the points did not match the name.

I am using Excel 2010.

I have attached a copy to make it easier for you to follow the above. Any help or guide would be appreciated.

Many thanks.

Book2.xlsx

[FDibbins]

If I understand you correctly, try this ARRAY formula in D9, copied down...

=IF(A9="","",LARGE(IF(Scores!$E$2:$E$150>=100,Scores!$D$2:$D$150),'Scorecard Sheet'!A9))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[pasqui83]

Wow thank you so much! Yes that worked perfectly for the D9 cell.However I still have the issue of finding the Team Type.

The top cells (C3:C5) in the scorecard heet where I need it to match the Team Type so I can pick out the top 5 positions for each team.

I tried to see if I could reference what you provided, but I am trying to match text and numbers and not sure how to create the criteria

I am now guessing it will be an index and match perhaps or just index?

[FDibbins]

I could do this for you real quick, but that wont help you much

If you look at what I showed you, the main part is this...
=LARGE(IF(Scores!$E$2:$E$150>=100,Scores!$D$2:$D$150),'Scorecard Sheet'!A9)
What this is doing is 1st, testing to see if Scores!$E$2:$E$150>=100, and for where it ism use the corresponding value in Scores!$D$2:$D$150, and then find the n-th largest value (A9)

Now, based on that, what you now need to do is add in an extra criteria that tests to make sure the corresponding value in sheet2 column C matches the criteria in sheet1 A3.

1 way to do this would be to add another IF statement...
=LARGE(IF(Scores!$E$2:$E$150>=100,IF(Scores!$C$2:$C$150='Scorecard Sheet'!$A3..... etc
OR, you can use the * character to combine the arguments...
=LARGE(IF((Scores!$E$2:$E$150>=100)*(Scores!$C$2:$C$150='Scorecard Sheet'!$A3)...ETC

Note that the test you have in sheet2 A4 (Releif) should match the data you are searching for (Relief Work)
Again, these are both ARRAY formulas. Let me know how you make out?

[pasqui83]

FDibbins You are indeed awsome! Thank you and I had no intentions on getting the code and pasting it in I've actually learnt something new and documented it as well so will be having a good play on this so it is inprinted in my mind. lol

Alot of my findings was using index and match became very confusing when attempting to put 3 different criterias together.

I used your first part of the coding as it was alot easier.

=LARGE(IF(Scores!$E:$E>=100,IF(Scores!$C:$C='Scorecard Sheet'!A3,Scores!$D:$D)),'Scorecard Sheet'!B3)

The one issue I found in the code is if you have 2 matching scores, it only looks at the first name with the top score. Is there a way to say look at the 2nd or 3rd? Does this change the code completley?

Again my complete thanks for your time!