Formula to lookup data using Concatenate -- needs to be able to give 1, 2, 3, or 4 based on data

[D Eckert]

I have a spreadsheet that tracks wrestler's points based on each match by weight class and pool.
They have three rounds, one match each round. the drop down options for the Win versus Loss determine the point values. Those all work fine.

There are two Pools: A & B and four teams in each

This sheet is titled Scoring and the A-Column contains and Concatenate formula =CONCATENATE(R2 & " ",S2 & " ",B2,U2)

R2 => A/B Pool (if a wrestler's team fell into the A Pool or the B Pool
S2 => Rank (1,2,3, or 4)
B2 =>Weight Class (73,78,83, etc -- there are 16 weight classes with maximum of eight wrestlers in each)
U2=>Substitute

Example Result of Concatenate => A 4 73 OR A 4 73 S

We choose the S drop down in U2 if a wrestler made the finals (1st or 2nd place in his pool) but is unable to wrestle for a medal -- either they were disqualified from the Tournament for some reason or the wrestler left the Tournament

On a second spreadsheet, titled Team Scores, I use a VLookup to pull the wrestler's info for those that achieved a 1 or 2 in each pool

=VLOOKUP(F2 & " " & G2 & " " & A2,Scoring!$A$2:$S$150,3,FALSE)

I cannot figure out how to change this formula or what formula to use that will do look a wrestler's info via the Concatenate cell and if the S portion is populated, to then move to the next ranking. Second would become first and third would become second.

I am trying not to have to manually edit this spreadsheet in the case of needed substitutions for the finals, nor do I want to have to add 4 wrestler's names for each of the 16 weight classes manually.

 

[Hui]

Can you please post a sample file so we can give you more specific advice

 

[D Eckert]

Please see attached for the example

(There is a separate post for the ranking issues I am having -- which is why only a few of the ranking items are filled in.)

http://chandoo.org/forum/threads/ra...points-and-personal-record.21448/#post-129369

 

[D Eckert]

OKAY< this became a mute issue because I made some changes to my SCORES spreadsheet that allowed for a DSQ or Left the Tournament column that the ranking took into account from the start.

I have a spreadsheet that tracks wrestler's points based on each match by weight class and pool.
They have three rounds, one match each round. the drop down options for the Win versus Loss determine the point values. Those all work fine.

There are two Pools: A & B and four teams in each

This sheet is titled Scoring and the A-Column contains and Concatenate formula =CONCATENATE(R2 & " ",S2 & " ",B2,U2)

R2 => A/B Pool (if a wrestler's team fell into the A Pool or the B Pool
S2 => Rank (1,2,3, or 4)
B2 =>Weight Class (73,78,83, etc -- there are 16 weight classes with maximum of eight wrestlers in each)
U2=>Substitute

Example Result of Concatenate => A 4 73 OR A 4 73 S

We choose the S drop down in U2 if a wrestler made the finals (1st or 2nd place in his pool) but is unable to wrestle for a medal -- either they were disqualified from the Tournament for some reason or the wrestler left the Tournament

On a second spreadsheet, titled Team Scores, I use a VLookup to pull the wrestler's info for those that achieved a 1 or 2 in each pool

=VLOOKUP(F2 & " " & G2 & " " & A2,Scoring!$A$2:$S$150,3,FALSE)

I cannot figure out how to change this formula or what formula to use that will do look a wrestler's info via the Concatenate cell and if the S portion is populated, to then move to the next ranking. Second would become first and third would become second.

I am trying not to have to manually edit this spreadsheet in the case of needed substitutions for the finals, nor do I want to have to add 4 wrestler's names for each of the 16 weight classes manually.