How to return cell value based on number of occurences

Rather than reproduce what you have given before being able to try things out, it would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

Ok, I am sending the example workbook with the described layout. I am having problem getting values to B3,B4,B5...B44,B45,B46 cells in individual team sheets based on Schedule sheet while using order of a game at cell B2, B43.

First of all, I changed your formula in AO4 of the Schedule sheet to this:

=IF(A4="","",VLOOKUP(B4,$AR$4:$AT$15,3,0))

and the one in AP4 to this:

=IF(A4="","",VLOOKUP(C4,$AR$4:$AT$15,3,0))

as they are much shorter. Then I put this formula in AM4 (coloured blue):

=IF(AO4="","",AO4&"_"&COUNTIF(AO$4:AP4,AO4))

and this one in AN4 (also blue):

=IF(AP4="","",AP4&"_"&COUNTIF(AO$4:AP4,AP4))

All of these were copied down to row 156.

Then in the individual team sheets I put the 3-letter code for the team (also the sheet name) in cell A1, and these formulae in the cells stated:

B3: =IFERROR(INDEX(Schedule!$A:$A,MATCH($A$1&"_"&B2,Schedule!AM:AM,0)),IFERROR(INDEX(Schedule!$A:$A,MATCH($A$1&"_"&B2,Schedule!AN:AN,0)),""))

B4: =IF(COUNTIF(Schedule!$AM:$AM,$A$1&"_"&B2),"H",IF(COUNTIF(Schedule!$AN:$AN,$A$1&"_"&B2),"R",""))

B5: =IF(B4="H",INDEX(Schedule!$C:$C,MATCH($A$1&"_"&B2,Schedule!$AM:$AM,0)),INDEX(Schedule!$B:$B,MATCH($A$1&"_"&B2,Schedule!$AN:$AN,0)))

That block of 3 cells can be copied into B44:B46, as well as into the corresponding cells of the other team sheets.

Hope this helps.

Pete

That is wonderful, thank you very much. You helped me a lot. Can I buy you a daiquiri or two?

Glad to help.

You know, you are the second person on here to offer me a drink in the last week (mind you, the other offer was for a beer). Of course, we give our help for free, so the only thing I would ask is for you to click on the "star" icon ("Add Reputation") in the bottom left corner of a post that you have found to be helpful - you can also leave a (private) comment if you wish to. You can do this in other threads that you find useful, as a way of "rewarding" the contributors.

Hope this helps.

Pete