Index Match Multiple Criteria - Need Multiple Results

[Sheetsforteach]

Hi all, first post so sorry if I get anything wrong:

I'm using the following formula (as an array) to return a name based on a score given the criteria that the name falls into a particular category.
=INDEX('Student Overview'!$J$3:$J$744,MATCH(1,("Year 7"='Student Overview'!$F$3:$F$744)*(Analysis!K19='Student Overview'!$I$3:$I$744),0))

Analysis!K19 is the following formula (also as an array):
=LARGE(IF('Student Overview'!$F$3:$F$744="Year 7",'Student Overview'!$I$3:$I$744),1)

'Student Overview'!$F$3:$F$744 is the list of categories
'Student Overview'!$I$3:$I$744 is the list of scores
'Student Overview'!$J$3:$J$744 is the list of names

The large formula is returning 2 values which are the same. When I run the index formula, this only returns the first match for these values so I can't see the name for the second tied score.

Any help much appreciated.

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please DO NOT attach a picture of an Excel sheet (I do not have the patience to re-type any/all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. For example, don't show text in a column if it's really a number. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually). To be honest, I am not interested in seeing a non-working formula... or a pile of blank cells. However, I am very interested in seeing your EXPECTED results in their EXPECTED location.

4. Try not to use merged cells. They cause lots of problems and are DEFINITELY best avoided!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Sheetsforteach]

Thanks for the quick response Glenn.

I've attached the file below (hopefully) and have added in both the current formulae I'm using (adapted to work with the sheet I've attached) and a mock up of the desired outcome. I've also added a couple of notes beneath the table to hopefully make linking it back to my original post a little more obvious.

Thanks, and have fun

[Glenn Kennedy]

In f2, copied down:

=INDEX($C$2:$C$20,MATCH(1,INDEX(($A$2:$A$20="year 7")*($B$2:$B$20=E4)*(COUNTIF($F$3:F3,$C$2:$C$20)=0),0),0))

no need for array entry.

[Sheetsforteach]

Amazing! Thanks Glenn. If you have chance, and don't mind, could you take me through what you have put together? It looks to me like an additional array multiplication with the countif, where the countif is looking at the values already returned in the name array??

[Glenn Kennedy]

Sure. The addition of an INDEX(blah blah,) removes (in this case) the need for array entry. Just a wee trick of the trade.

And yes, all I did was add a countif...

returns TRUE or FALSE... if a name has already been returned as a result, the COUNTIF will returrn 1 or more... and so COUNTIF()=0 will return FALSE. So, it effectively removed duplicate names. If the names aren't unique, you'd need to use ID number... or another workaround.




You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Chrys_1981]

Hi,

I would like to create a formula in the attached excel where i can use multiple criteria in rows and columnes but also get values that are less than a particular value. Can you please help?

[Glenn Kennedy]

Sure thing. But....

Hi.

It's a forum rule that you start your own thread, rather than try to "hijack" someone else's thread. This helps prevent massive confusion arising -which will happen if it is not clear which question is being answered. So please start your own thread and explain your own problem (make sure that, right from the start, you use a meaningful title (NOT things like "help needed" or "urgent problem"... think of the Google search terms that you would use to find the solution). Also, preferably attach an Excel sheet. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Just before you submit your post, click on GO ADVANCED (near the bottom) and scroll down to "Manage Attachments" - click that and then follow the instructions.