INDEX(...MATCH(IF...))) Function trouble

Hi guys,

Go easy on me please, I'm trying to get the hang of Index array functions but I'll freely admit I don't know what I'm doing.

Trying to extract the top 10 best/worst performing teams in terms of completion of various training course; 433 teams spread across 3 "Groups".

I've attached a sample of what I'm doing (any identifiable info has been removed leaving the rather unimaginative team names of "team 1, team 2 etc...),

So what I need to do is extract the lowest 10 performers from group A in terms of percentage completion of course 1, then do the same for group B and group C.

The Index function I've cobbbled together so far from various sites (this one was particularly helpful) doesn't seem to work, but I can't work out why:

Please Login or Register  to view this content.

I'm putting the index function in cell B6, and asking it to refer to range $A$6:$A$15 for the k-th value. Then I've got a simple vlookup in range C6:C15 to look up the actual percentage for the team returned in column B. There may be more eloquent ways of doing that but it works, if only the index function did.

All i'm getting is 10 identical results, all team 24 (or whatever the first row it finds with 0% seems to be, even if I sort the data different ways)

So, any ideas please? Not sure what the rules are with index arrays - does the data need sorting in any particular way etc... for this to work?

Or if anybody would be so good to suggest a completely different function/method of doing this, I'd be grateful (My point is to avoid having to pivot and paste values. I want to be able to populate range ("A21:G" & LR) with data and have the tables work it out automatically)

thanks

Hi reaper_2

find the attatched file

Kindly ignore the my previous post and take the attatchment with this post

Hi reaper_2, wellcome to the forum...

Please, rename your title according to the rules:

1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more hours have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

Originally Posted by zbor

Hi reaper_2, wellcome to the forum...

Please, rename your title according to the rules:

1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more hours have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

Please advise what would be a more appropriate thread title?

Originally Posted by Azam Ali

Kindly ignore the my previous post and take the attatchment with this post

thanks a lot for that, just what I needed.

Don't suppose I could ask you to explain how it works? I've not used these functions before but would like to be able to adapt this one to other worksheets. Any explanation of what's happening in the INDEX function (e.g. B6) would be much appreciated.

Originally Posted by reaper_2

Please advise what would be a more appropriate thread title?

As it said: Use terms appropriate to a Google search. Think what would you want to Google to give you result for your problem...

It would really help us to see each post among hundreds of post each day.

About excel table:

In C6 you have =SMALL(IF($B$21:$B$454=$B$4;$C$21:$C$454);ROWS($C$5:C5))
Which will give you smallest value with condition ($B$21:$B$454=$B$4).

Now you need to see to which Team does it go to.
So:

=INDEX($A$21:$A$454;LARGE(IF((($B$21:$B$454=$B$4)*($C$21:$C$454=$C6));ROW($A$21:$A$454)-ROWS($B$1:$B$20));COUNTIF(C6:C$15;$C6)))

It also look for your value in column B4 and value in column C6, C7, C8 etc and extract n-th Largest number and return Team. You need it in case of same values (0%, 0%, 0%...) otherwise it would always return first value.

Click in formulas -> Evalueate Formulas and see how it work.

For easier adoption to other worksheets you can use:

=INDEX($A$21:$A$454,LARGE(IF((($B$21:$B$454=$B$4)* ($C$21:$C$454=$C6)),ROW($A$1:$A$434)),COUNTIF(C6:C$15,$C6)))

Where:

A are your results
B and C are your conditions
ROW is now slightly shorten

And dont' forget to comfirm it with ctrl+shift+enter (curely { } brackets must appear around formula) or you'll get #VALUE!


You can create several tables for each gruop or you can create drop down menu in B4 with each name and change it.

Originally Posted by zbor

As it said: Use terms appropriate to a Google search. Think what would you want to Google to give you result for your problem...

It would really help us to see each post among hundreds of post each day.

About excel table:

In C6 you have =SMALL(IF($B$21:$B$454=$B$4;$C$21:$C$454);ROWS($C$5:C5))
Which will give you smallest value with condition ($B$21:$B$454=$B$4).

Now you need to see to which Team does it go to.
So:

=INDEX($A$21:$A$454;LARGE(IF((($B$21:$B$454=$B$4)*($C$21:$C$454=$C6));ROW($A$21:$A$454)-ROWS($B$1:$B$20));COUNTIF(C6:C$15;$C6)))

It also look for your value in column B4 and value in column C6, C7, C8 etc and extract n-th Largest number and return Team. You need it in case of same values (0%, 0%, 0%...) otherwise it would always return first value.

Click in formulas -> Evalueate Formulas and see how it work.

thanks for your help.

Feel free to change the title to whatever you see as appropriate. I'm a bit miffed, as ironically enough the formula I'd started with was cobbled together by googling - among other things - Excel "Index Function" Match problems which brought up 128,000 hits, the first page of which were all useful to me. As far as I can see this is dependent on being able to properly use a google search for research purposes (i.e. use inverted commas around your primary subject and use only a few additional keywords to narrow down your results.

Also be aware that a google search will bring up the contents of the post and not just the title.I've been on so many forums which preach against having war and peace as a subject heading ,and would be far happier with a title such as the one I've gone with here.

Anyway having said all that, I'll bite my tongue and follow procedure in future. Thanks again for the help with the formula