Index/Match Formula

[SimonGeoghegan]

Hi All,

I have a table with a number of risks in it across 50+ departments. I have a formula which provides me with the top 5 risks (based on risk score) across the entire building as a whole, however, I want to adapt this so that when I change the department via a combo box with a Cell Link and subsequent named range of "Department_Description" which just runs an index against the cell link to give me the name of the department.

The formula I have so far is:

=INDEX(Risks!$D$2:$D$2625,MATCH(1,INDEX((Risks!$E$2:$E$2625=LARGE(Risks!$E$2:$E$2625,ROWS(D$1:D1)))*(COUNTIF(D$1:D1,Risks!$D$2:$D$2625)=0),),0))

For the above:

Column D = column with Risk Names
Column E = column with Risk Scores

The column contain Department Names is Column A

This works brilliantly but I need to try and take it to the next level now however I don't have the knowledge to get to that stage.

If anyone can assist/advise on how I may be able to amend the formula above to take into account a department selection, I would very much appreciate it!

Hopefully it can be achieved!

Regards,
Simon

 

[Aladin Akyurek]

Re: Help with Index/Match Formula

Are you wanting to list top 5 risks given a department?

 

[SimonGeoghegan]

Re: Help with Index/Match Formula

Hi Aladin,

Yes, I want to select a department from the combo box and then the formula show me the top risk for that department (I'd then drag the formula down so that it changed to give me the 2nd risk, 3rd risk, 4th etc.)

 

[Aladin Akyurek]

Re: Help with Index/Match Formula

Hi Aladin,

Yes, I want to select a department from the combo box and then the formula show me the top risk for that department (I'd then drag the formula down so that it changed to give me the 2nd risk, 3rd risk, 4th etc.)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][/tr][tr][td]

1​

[/td][td]Dep[/td][td]Risk[/td][td]Risk Score[/td][td][/td][td][/td][td]sales[/td][td]

6​

[/td][/tr]
[tr][td]

2​

[/td][td]sales[/td][td]r-1[/td][td]

2000​

[/td][td][/td][td][/td][td]r-6[/td][td]

6000​

[/td][/tr]
[tr][td]

3​

[/td][td]sales[/td][td]r-9[/td][td]

2000​

[/td][td][/td][td][/td][td]r-2[/td][td]

6000​

[/td][/tr]
[tr][td]

4​

[/td][td]sales[/td][td]r-4[/td][td]

3000​

[/td][td][/td][td][/td][td]r-3[/td][td]

4000​

[/td][/tr]
[tr][td]

5​

[/td][td]sales[/td][td]r-3[/td][td]

4000​

[/td][td][/td][td][/td][td]r-4[/td][td]

3000​

[/td][/tr]
[tr][td]

6​

[/td][td]adm[/td][td]r-2[/td][td]

4000​

[/td][td][/td][td][/td][td]r-1[/td][td]

2000​

[/td][/tr]
[tr][td]

7​

[/td][td]adm[/td][td]r-6[/td][td]

2000​

[/td][td][/td][td][/td][td]r-9[/td][td]

2000​

[/td][/tr]
[tr][td]

8​

[/td][td]adm[/td][td]r-8[/td][td]

2000​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]accounting[/td][td]r-8[/td][td]

1000​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]sales[/td][td]r-6[/td][td]

6000​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]accounting[/td][td]r-9[/td][td]

6000​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]sales[/td][td]r-2[/td][td]

6000​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td]adm[/td][td]r-5[/td][td]

3000​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In I1 control+shift+enter, not just enter:

=SUM(IF($A$2:$A$13=$H$1,IF($E$2:$E$13>=LARGE(IF($A$2:$A$13=$H$1,$E$2:$E$13),MIN(5,COUNT(IF($A$2:$A$13=$H$1,$E$2:$E$13)))),1)))

In H2 control+shift+enter, not just enter, and copy down:

=IF($I2="","",INDEX($D$2:$D$13,SMALL(IF($A$2:$A$13=$H$1,IF($E$2:$E$13=$I2,ROW($A$2:$A$13)-ROW($A$2)+1)),COUNTIFS($I$2:I2,I2))))

In I2 control+shift+enter, not just enter, and co[y down:

=IF(ROWS($I$2:I2)>$I$1,"",LARGE(IF($A$2:$A$13=$H$1,$E$2:$E$13),ROWS($I$2:I2)))

 

[SimonGeoghegan]

Re: Help with Index/Match Formula

Aladin, thank you very much - this works superbly! Very much appreciated!

 

[SimonGeoghegan]

Re: Help with Index/Match Formula

Also, if anyone can let me know how I mark the thread as resolved, i'll certainly do this!

 

[Aladin Akyurek]

Re: Help with Index/Match Formula

Aladin, thank you very much - this works superbly! Very much appreciated!

You are welcome.

Also, if anyone can let me know how I mark the thread as resolved, i'll certainly do this!

Don't worry. No special action is required.