Index/match statement for multiple answers

Hello, i have a problem that is a bit perplexing for me.

I work for a hospital and the administrators are inquiring as to what hours during the day are the busiest for admission. I am trying to come up with a way to "automate" the answers, rather than look through a table and count.

I know I can find the highest amount of admissions by utilizing the =MAX statement. The issue is telling me what hours are the busiest when there are two or more hours with the same answer.

For example, if one looks at the attached spreadsheet for the month of January, the highest number of admissions taken is 3 which would give 1400 & 1600 hours as the busiest times. Is there a way to provide both times in an answer? I tried =INDEX($A$4:$A$27,MATCH(B30,$D$4:$D$27,0)) but that only provides one of the two hours (1400) as an answer.

Thank you very much in advance,

Lance

Hi ljbrown,

you could use the following:

=INDEX(INDIRECT(CONCATENATE("$A$",MATCH($B$30,$B$1:$B$27,0)+1,":$A$27")),MATCH($B$30,INDIRECT((ADDRESS(MATCH($B$30,$B$1:$B$27,0)+1,2)&":$B$27")),0))

Put this in the cell below the first time that is found, what it does is tell excel to start the match function from the cell below the first value found.

So the first value 14:00 is found in B18, so that will tell excel to run the match function from B19:B27 and return the corresponding data found in A19:A27

The only downside to this would be that it would find the 2nd MAX time, but if there is a 3rd or 4th you wouldn't find this.

Hope this helps
Dan

Hi Lance,

Using the previous method will get quite messy in that case. If you have no issues with having a hidden column to the left of the data, then this method will work the best, and is a combination of formulas which Is much easier to understand.

Im on IE at work so the upload button wont work for me, so insert a column to the left of column A

In A4 insert the following formula:

=IF(C4=MAX($C$4:$C$27),"Busy Hours "&COUNTIF($C$4:C4,MAX($C$4:$C$27)),"")

What this will do is check if it is one of the MAX admissions, and if it is it will assign it with "Busy Hours 1" then "Busy Hours 2" and so fourth.

Drag this to the bottom of you data set and then it is a simple vlookup from the table you have made at the bottom.

=IFERROR(VLOOKUP($B31,$A:$B,2,0),"") - That will lookup "Busy Hour 1" and return 14:00

Any issues let me know.

Many Thanks
Dan