Excel 2007 : Using INDEX and MATCH (or other) to return cell based on largest associated value

Hi,

I am having trouble with using INDEX and MATCH (open to using other formulae too) to tell me the cell based on the largest value in a table.

My WIP formula:
=INDEX(Score!A3:AU813,MATCH(LARGE(Score!B3:AU813,1),Score!A:A,0),MATCH(J2,Score!B1:AU1,0))
I think the problem is there, though I am not sure about that.

Basically I want to find the highest value in a single column between Score!B3 and Score!AU813 with the column based off Output!J2 (current sheet) and give me the cell value of Score!A3:A813.

Example, I enter 73 for J2 in the Output sheet and it should use column AN in the Score sheet to check for the largest value (ignoring the first 2 rows which are background codes) and return the row of that largest value and use the value in column A of that row as the result.

As it is possible to have multiple same largest values, how would I handle those best on my Output sheet? I am looking at the 5 largest values so I guess there is room for 5 same top values. So on the Output sheet is it possible to add a handler to the 2nd-5th row under "Outcome" to ignore/skip the value of the fields above?

Example: If A6 = A5, go to next highest value in the score sheet and give me that instead.


Worksheet attached.
Attachment 131737

Thanks!

Try this to start with.

Define Dynamic Ranges
1/. Name:= "ActorID"
Refers to:=

Please Login or Register  to view this content.

2/. Use this for Data Validation in Output J2
Allow:= List
Source:+

Please Login or Register  to view this content.

2/. Name:= "ReqdCol"
Refers to:=

Please Login or Register  to view this content.

Then with Sheet "Output"
in A5

Please Login or Register  to view this content.

in B5

Please Login or Register  to view this content.

in C5

Please Login or Register  to view this content.

Select from the Dropdown In J2

Hope this helps

Hi Marcol,

Thanks for your effort - though I think something didn't quite work right. Part of this is really my fault for not including the information from my previous threads.

The average and count fields I already have codes for and they are based off the raw data that is not included in the uploaded workbook.
The Score sheet is a compilation of all Outcomes versus actors with Average score value of each outcome already calculated.

As such, once A5 is determined (and A6,7,8,9 for that matter):
the associated B row would run:
=INDEX(Score!A3:AU813,MATCH(A5,Score!A:A,0),MATCH(J2,Score!B1:AU1,0))

In C row it would run:
=COUNTIFS(Sheet1!D:D,A5,Sheet1!AS:AS,J2)

So the problem I am having is how do I tell Excel to retrieve A5/6/7/8/9 for me as a ranked top1-5 (order doesn't matter if they have the same max value in columns B to AU in Score)

For reference, for ActorID 73, the highest average value is 9 and that corresponds to Outcome164

Hope that helps


Some more background on my problem:
http://www.excelforum.com/excel-2007...age-score.html

Try this workbook, it uses a combination of native formulae and two UDFs as examples.

This ranks all the data for each actor.
You will not require every column and row, but you should be able to manipulate the selected data to suit your needs.

I have put the workbook on My Skydrive in case the attachment mystically disappears.
ScoreTest_dip11.xls

The Forum Attachment facility seems to be broken, try the skydrive link.
You might have to sign up first, it's free.

Thanks Marcol, that's exactly what I was looking for

I'll give you another rep point as soon as the forum allows it again (need to spread more around before giving you another or so)

In the meantime, I have finally managed to anonymize my actual data table and was wondering if you would be willing to adjust the code for this? I think you will find a lot of the code can be cut/simplified due to the availability of certain data on the various sheets.

Sorry for the bother!

I am not sure I fully understood. Try these formulas.

All are in Output sheet.

B24,

=IFERROR(LARGE(INDEX(Score!B$3:AU$813,0,MATCH(J$2,Score!B$1:AU$1,0)),1),"")

B25,

=IFERROR(LARGE(INDEX(Score!B$3:AU$813,0,MATCH(J$2,Score!B$1:AU$1,0)),1+COUNTIF(INDEX(Score!B$3:AU$813,0,MATCH(J$2,Score!B$1:AU$1,0)),">="&B24)),"")

Copy down.

A24;

=IFERROR(INDEX(Score!A$3:A$813,MATCH(B24,INDEX(Score!B$3:AU$813,0,MATCH(J$2,Score!B$1:AU$1,0)),0)),"")

Copy down.

I'm at a loss to understand what you are asking.

ActorID, ReqdCol, and Outcome are all dynamic defined named ranges (see the names manager in 2007)

ReturnOutcome and ReturnOutcomeDirect are UDFs (User Defined Function), you need to copy the VBa module to your workbook.

What are you trying to do with the formulae in Sheet "Output" A24 and B24? (Use the names as in this sample)

What is the purpose of "CODE" in A6:B21 ?

How is Pmonth in B2 derived?

See if this workbook is closer to your needs, select an actor from the dropdown in A2

Hi Marcol,

CODE in A6:B21 is a placeholder for Excel2007 based code which would throw up errors for you in Excel 2003. Is it not relevant to this particular question as it is working code. Just my way of saying "don't use or refer to this particular space as it is already used"

Pmonth is derived from ProjMonth in Sheet1. It isn't currently used for this problem, but I may consider using it in future as a filter, i.e. to show the top 5 scores for a given month as opposed to top 5 scores for the entire period. It is used by the Excel 2007 code that I have omitted in the provided spreadsheet.

The purpose of B 24-28 is to draw the highest 5 scores from the Score sheet for the relevant Actor. For Actor3, that is 10, 10, 8, 8, 8
The purpose of A 24-28 is then to match the relevant OutcomeID to the score in B24-28 respectively.

So for Actor3 with ActorID 11, the first OutcomeID (A24) would be Outcome4 at a score of 10 and Outcome378 at a score of also 10 in A25, then Outcome38 in A26, Outcome117 in A27 and Outcome535 in A28 which all match a value of 8 in B26-28.

The Count C24-C28 as I said can be ignored as part of this question as what it will do is to count the instances of A24-28 in Sheet1 and display that number for that actor. The code I have written works fine for that, but is Excel2007 code.

The spreadsheet you provided a few posts back works perfectly for that and I am trying to implement the Outcome for A24-28 and Top Ratings for B24-28 in a modified format.

What I have already done for B24-28 is adjust it to the actual spreadsheet which is why it displays 10, 10, 8, 8, 8 currently - Just like it does in Top Ratings.

The trouble I have is moving the Outcome part over to my spreadsheet as well as possibly simplifying the formula you used for both TopRatings and Outcome.
It is possible that ReqdCol could be simplified given the full spreadsheet.
What I cannot manage also is to get the Outcome to work without the instance and In Row No columns.

I hope that helps

See the attached.

Does this work for you?

Okay, back sooner than expected.
Try this workbook.
Select the required actor from the dropdown in A2 and the number of results to return from C23
The result is based on the score ranking, so if there are, say 10 outcomes in first position then all 10 will be returned if you call for, say the top 5.
If there are clear first, second, third, etc results, then the top 5 (or requested number) will be returned.
To see what this means unhide the helper columns by clicking the grouping button.

Use the dynamic named ranges, they should handle any amount of data you put in Sheet "Scores"
See this link about Dynamic Names Excel Hero - The Imposing INDEX

Hi Haseeb,

Your formula works fine in that workbook, but when I copy the formula itself (mark formula in formula bar) and transfer it to my main workbook, it doesn't work anymore. Considering all the data and naming is the same, I'm a bit at a loss here.

I do notice though that in your uploaded sheet, there are {} brackets in the formula bar which disappear when copied. Maybe that is the problem I am having?

Much appreciate if you could enlighten me on how to fix this

Marcol: Yours still works, though I can't seem to access to view/edit the UDFs you wrote though. When I click on Macros-View Macros, it is empty?

It wont show with "View Macros" because it's a Function that requires arguements.

Press Alt+f11 to see the code (any version of Excel), or , I think it is Developer > Visual basic Editor in 2007

[EDIT]
To get the braces {} with Haseebs' formula confirm it with Ctrl+Shift+Enter not just Enter
Dont try adding the braces manually, that will just return a text string (the formula in braces).

I dont have 2007 available at the moment, so testing speed would be a bit meaningless.
I suspect that there will not be much difference with the two solutions, although array formulae (CSEs) tend to slow things a bit more than non-array solutions.

The major advantage with the solution I offered is that it is flexible and completely dynamic, and will work without any changes, regardless how large or small your "Scores" table is.
This will be slower than a non-dynamic solution, but it should not be significant compared to the time lost changing ranges as the table increases in size, or the processing time is lost when the table is small compared to the static range.

[EDIT]
Google:= Excel 2003 MINIF/MAXIF
You'll find plenty solutions there.

Hi,

I was testing something with Haseeb's formula and noticed that if drag down the list further in Output than there are valid entries in Score, it will display 0 in the first Outcome instance where there are no more values and then cycle through Outcome in turn (Outcome1, Outcome2, Outcome3....)

Is there a way of getting it to show blank if there aren't any more valid entries?

Furthermore, I realize I already marked this as solved as my original issue is indeed solved thanks to 2 great contributors here, but I was curious if it were possible to cut out the Scores sheet completely and base it all off Sheet1 and Output only? Of course possibility is 1 thing, efficiency another. So if it were possible, but it be more or less efficient?

Cheers!

I'm not sure about how the months work but I used this formula in B24 confirmed with CTRL+SHIFT+ENTER and copied down

=LARGE(IF(Sheet1!C$2:C$10000=J$2,IF(Sheet1!D$2:D$10000=B$2,Sheet1!B$2:B$10000)),ROWS(B$24:B24))

and this one in A24

=INDEX(Sheet1!A$2:A$10000,SMALL(IF(Sheet1!C$2:C$10000=J$2,IF(Sheet1!B$2:B$10000=B24,ROW(Sheet1!C$2:C$10000)-ROW(Sheet1!C$2)+1)),COUNTIF(B$24:B24,B24)))

Where B2 is ProjMonth. Note that I deleted Scores sheet so all data comes from Sheet1

There's no error checking there but that can be added - do you need it to be compatible with Excel 2003 or is 2007 OK?

See attached

OK, Yes, I was thinking it right...but doing it wrong .....I missed out a condition, so formula in A24 should be.....

=INDEX(Sheet1!A$2:A$10000,SMALL(IF(Sheet1!C$2:C$10000=J$2,IF(Sheet1!D$2:D$10000=B$2,IF(Sheet1!B$2:B$10000=B24,ROW(Sheet1!C$2:C$10000)-ROW(Sheet1!C$2)+1))),COUNTIF(B$24:B24,B24)))

confirmed with CTRL+SHIFT+ENTER and copied down, see revised attachment

My suggestion is using a month criteria, so those are the top 5 for actor ID 11 in month 27 (shown in B2). Can you confirm what the month criteria should be?

You must be expecting a month restriction because if it's just top scores for Actor ID 11 then there are 5 * 10, 1 * 9 and 10 * 8

I spent a bit of time over the weekend looking at this problem, based on DLLs idea.

If you have duplicate outcomes then DLLs solution fails, it needs another condition added.
I have assumed that you need to ignore scores that are blank.

This workbook should return the top rankings for the selected actor and also filter the results for the selected pmonth.
Both groups are independently calculated therefore they can return seemingly conflicting results, this is to be expected.

Select the required actor from the dropdown in A2, and the PMonth from B2.
The list in B2 will change for each Actor, but mainly it will look the same due to available data.

Select the number of results to be returned from the dropdown in C24.
This returns results based on rankings, not a straight count of the results as found, so you can often get more results than you might expect.

The workbook is dynamic and should not require any adjustment as your data grows or shrinks. It's biggish, I'm still restricted to 2003.

Hope this helps.

Originally Posted by Marcol

If you have duplicate outcomes then DLLs solution fails, it needs another condition added.

Hello Marcol......how dare you question my methods

Seriously though can you explain what you mean? The last solution I posted seems to agree with yours, e.g. on your sheet if I put 27 in B2 then the top 5 results in I25:J29 are exactly the same as mine in A24:B28 - the top 5 scores in month 27 for actor ID 11 with associated outcomes. Of course there are additional tied scores (8) which would be in positions 6 to 10 but my existing formula would pick those up if copied down further.

Do you understand what logic would show the results that dip11 describes in his last post because that doesn't seem to match what either of us is doing?

Hello Marcol,

Yes, you are absolutely correct, my mistake. I was playing around with that shorter formula but then realised the same as you, that it wouldn't work because there are multiple repeats of each outcome. If you look at my post with the attachment the formula in the post is what I meant to include in the sheet......

...somehow I kept the wrong formula in the worksheet....

That formula is almost the same as the one you are suggesting, so yes, I think we agree......just got to make our versions agree with the dip11