Search for a date that matches, then match a name from a list, then match the score for that name

[bristolrob]

So i am creating a spreadsheet for my skittles (bowling) team, i need to match 2 dates together from separate cells, then match the name of the player from a list of players and then finally record the score the player got.
Dates are in B2 and C21. The player name is in B25 with the player list B7:B17 and the score will be in I7:I17
The formula will go in C25 next to the player name
I have been messing with nested IF formulas and cannot get it right
Many thanks in advance

 

[jasonb75]

What are the dates being compared to?

I'm going to assume that there will not be any duplicate entries of any given date / player combination? If that is true then SUMIFS would be the best way to go. I've used the same method in this thread so that you can see an example of how it would work.

Formula - index match

Hello, What formula would be easier to get the prices across from the above table set, because there is empty cells index match is not working? Thank you Bat

www.mrexcel.com

 

[bristolrob]

Here is the table i need to return the score from
I want Alex F's score next to his name in the lower list
The date/name will be unique for each League Game
Dates are in B2 and C21. The player name is in B25 with the player list B7:B17 and the score will be in I7:I17
The formula will go in C25 next to the player name
Would be an added advantage if the score could be recorded as text so that i do not need to create a new sheet for each game
Many thanks for the assist guys

 

[jasonb75]

So you're saying that you want to change the date in B2 so that the results are assigned to the correct column in the bottom table, with the top table being cleared and reused each time?

That is not going to be possible with formulas.

 

[bristolrob]

So you're saying that you want to change the date in B2 so that the results are assigned to the correct column in the bottom table, with the top table being cleared and reused each time?

That is not going to be possible with formulas.

That would be the ideal solution yes, but if not possible with formulas i can simply create a new tab for each game, no drama

 

[jasonb75]

With a sheet for each game, you could use a simple vlookup formula to find the name and return the score. Based on the screen capture.

=IFERROR(VLOOKUP($C25,Sheet1!$B$7:$I$17,8,0),"")

Note that you would need to change Sheet1 to the name of the sheet for the game of interest. If you want to name the sheets based on date and use the dates in row 21 as part of the formula then it will get more complicated. You would not be able to use the dates in the same format because you can not have the / character in a sheet name. I would need the format of the dates used to name the sheet in order to provide the correct formula.

 

[bristolrob]

With a sheet for each game, you could use a simple vlookup formula to find the name and return the score. Based on the screen capture.

=IFERROR(VLOOKUP($C25,Sheet1!$B$7:$I$17,8,0),"")

Note that you would need to change Sheet1 to the name of the sheet for the game of interest. If you want to name the sheets based on date and use the dates in row 21 as part of the formula then it will get more complicated. You would not be able to use the dates in the same format because you can not have the / character in a sheet name. I would need the format of the dates used to name the sheet in order to provide the correct formula.

Thanks very much Jason
The format I will use is simply 20102021 01112021 etc unless Game1 Game2 etc would be less problematic ?? will go with whatever you would suggest as easiest

 

[jasonb75]

I would say use the dates, if you wanted to use Game1, etc then you would need to add that information to the results table as well, where as the dates are already there, I just needed to know how you would name it so that I could format it correctly.

=IFERROR(VLOOKUP($C25,INDIRECT("'"&TEXT(C$21,"ddmmyyyy")&"'!$B$7:$I$17"),8,0),"")