Using index and match to find several values
Hello. I have administered a test for my students where they answered many questions which then give scores to each of 8 areas of "intelligence." I use a formula, INDEX($CS3:$CZ$4,1,MATCH(LARGE(CS3:CZ3,1),$CS3:$CZ3,0)) to search the 8 values and select the largest and return the title of that intelligence which is in the column header, row3.
My problem, or one of them, is that some students have more than one area of intelligence tied for the highest score. As it stands, I onmly report the area which is the first that Excel finds on the left. I would like to have Excel return all the areas (column titles) for the columns that share the highest scores. If the high score is unique, I'd get the current result. If there is a tie, I'd get 2 results etc.
Vlookup, index and match are about as advanced as I get with Excel at the moment, but I'd love to learn how to solve this problem. The end goal is to help my entire school program better for our students, so it's a worthy cause.
Thanks,
Josh
Hello
I've attached a copy of your example file with a possible solution using array formulas in the blue shaded area at the end of you score table. I've allowed for 8 possible ties of the max value but if this is unlikely, then you can allow for as many as you wish. These are array formulas and must be entered using Ctrl+Shift+Enter.
Just noticed that you're using Excel 2003. I thought you were using 2007 as that was the format of your file. The attached file and formula will not work in 2003 as it uses the Iferror function but I can change this to work in 2003. I will load a 2003 version as well.
Hope this helps.
Last edited by DBY; 12-19-2011 at 11:18 AM. Reason: Added last paragraph
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks