Formula help... Sorry for the long drawn out post. 1

[Kenja824]

I am attaching a file to help with matters... I believe I am looking for some form of Index/Match formula, but I am not sure.

In the file, there are two tabs. Yahoo and Formulas.

YAHOO tab = I will go into Yahoo football and find a box score for a game played. I will copy and paste the box score directly from yahoo website to this sheet. ... Important - Depending on what happens and which players perform well, the players names can fall on different rows of column A, and I think it is possible the stats could end up in different columns each time.

FORMULAS tab = Here I will have the formulas I want to search the box scores and return the specific stats I need.

This may sound like a straight up index/match formula, but here are my problems....
Depending on whether the player ran with the ball, caught the ball, or threw the ball, his name can appear multiple times.

As you will see the name "27 Kareem Hunt" appears in this case at row 11 & 29.
My formula tab will have cells looking for his yards but they need to find the correct cell whether it is his receiving yards or rushing yards.

Example: Cell J6 of the Formulas tab is looking for Hunts rushing yards in Yahoo tab. Cell N6 is looking for his receiving yards in the Yahoo tab. In this case, J6 would = YAHOO C11, and N6 would = YAHOO C29. Both ended up on "C" but there is no guarantee it will always be so.

How do you get a formula to return the correct cell if the name of the player can appear twice?
How do you get the formula to find the right category if the YDS category for receiving is in one row and the rushing yds is in another row and there is still yet more yds categories for passing, returns, and defense?

Important notes:
1) I am trying to not have to manipulate any info on the Yahoo sheet. This is strictly copied and pasted from a website. I will have to do this for multiple games each week.
2) I cannot be sure any particular name or stat will fall in any specific column or row from one box score to another.
3) I cannot be sure the players name will even be in a box score, so if it is not found, I need it to return "0"
4) One week I will be adding box scores for multiple games. The next week I will be adding more. I think all of the box scores in one week will be pasted on the same sheet below one another.

Im guessing it would need to look for the word Receiving (for example) and find the first cell named "Yds" in a row below the Receiving cell. Then do the same for the players name and do an index match using those two cells locations? I have no idea how to write that into a formula though. A standard Index/Match is deep enough for me to figure out. lol

If this is something better done in VBA, that is fine. I am not very good with that but it will give me more practice and learning opportunity.

Using Tables in this case I think is not an option if they would help because I would have to go into each box score and create a table every time.

Sorry for the long drawn out post. If this task is a very difficult one, it is not actually important. I am just looking to make it easier to keep track of while it is going on instead of having to read each box score and manually input their stats. If I get it done and it works easy enough I may consider using it for the regular season as well. lol

 

[SkipVought]

Great

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}