Formula for selecting content by text and number

[LearningExcel1984]

Hi all

I'm creating a Spreadsheet for my schools Sports Day. It's quite a simple document, but am trying to automate some of the functions a little, to help my PE department, make the day a little easier.

Problem is I'm stuck on one section, and was hoping for some advice and suggestions.

Currently this is an example of one of the Heat tables (please forgive Harry Potter references as example data).

Position and points are generated automatically depending on time. So far so good! (Using Rank and VLookup for that naturally).

However for the finals I need to select the fastest runner from each house. The problem for me however depends on how many pupils actually sign up for events. Some houses may have more pupils than other houses, which has caused me some head aches.

The only constant I do have, is that each table will only have a maximum of 20 pupils. I was playing around with =Index and Match, but with the number of pupils not entirely consistent it is causing some problems.

Any suggestions on possible formulas I can use?

 

[Saba Sabaratnam]

Hi,

Enter the array formula in cell F2 by pressing Shift + Control + Enter

=IF(C2=MAX(IF($B$2:$B$21=B2,$C$2:$C$21,0)),MAX(IF($B$2:$B$21=B2,$C$2:$C$21,0)),"")

Kind regards

Saba

 

[jtakw]

Cross posted here: https://www.excelforum.com/excel-ge...g-content-by-text-and-number.html#post4915216

 

[Peter_SSs]

Cross-posting is not against our rules, but the method of doing so is covered by #13 of the Forum Rules. Be sure to follow & read the link at the end of the rule too!

 

[LearningExcel1984]

Sorry, did not realise that was a problem. I won't make that mistake again!

Sorry again, this is a CrossPost from an earlier thread located here.

The problem in full is located in the OP, (I would edit the crosspost bit in but I can't edit posts?)

 

[LearningExcel1984]

Hi,

Enter the array formula in cell F2 by pressing Shift + Control + Enter

=IF(C2=MAX(IF($B$2:$B$21=B2,$C$2:$C$21,0)),MAX(IF($B$2:$B$21=B2,$C$2:$C$21,0)),"")

Kind regards

Saba

Hi thanks for this, I have tried it but unfortunately, the cell just remains blank. Does Excel 2016 Desktop support arrays?

 

[Saba Sabaratnam]

Hi

I assumed that data is in A1:E21 as given in your table.

if so, enter the array formula into F2 by pressing Shift + Control + Enter. If the formula is entered correctly, the formula will look this:

{=IF(C2=MAX(IF($B$2:$B$21=B2,$C$2:$C$21,0)),MAX(IF($B$2:$B$21=B2,$C$2:$C$21,0)),"")}.

(Do not enter curly brackets manually)

Then copy down the formula from F2 to F21.

Kind regards

Saba

 

[LearningExcel1984]

Hmmmmm

Does not seem to work for me, either typing it manually or copying and pasting.
All that happens is the cell stays blank.

It does show the formula in the bar with the brackets however. Pretty sure I'm pressing cntrl shift enter.

 

[LearningExcel1984]

Hey all

Just updating this thread. This problem has now been solved now so thanks to all here that tried to help. I ended up using this solution from the other thread.