Getting a VLOOKUP to say a line of text

[Steviefiege]

Hi,

I've got a list of players but i'll just narrow it down to 1 for the reason. At the side of 62.06 i want it to say "Taken" as there is already a player in that position, i just cant figure out the formula. I think it's a VLOOKUP but that just returns the players name & i dont want that. Any help would be really greatful.

Steve

[TABLE="width: 409"]
<colgroup><col width="124"><col width="157"><col width="64" span="2"></colgroup><tbody>[TR]
[TD="class: xl66, width: 124"]Butler, Andy[/TD]
[TD="class: xl66, width: 157"]Central Defender[/TD]
[TD="class: xl67, width: 64"]62.06[/TD]
[TD="class: xl68, width: 64"] [/TD]
[/TR]
</tbody>[/TABLE]

Boyle, Andy

Rowe, Tommy

Marquis, John

<colgroup><col style="mso-width-source:userset;mso-width-alt:5412;width:111pt" width="148"> <col style="mso-width-source:userset;mso-width-alt:3364;width:69pt" width="92"> </colgroup><tbody>
[TD="class: xl66, width: 148"]Goalkeeper[/TD]
[TD="width: 92"][/TD]

[TD="class: xl65"] [/TD]

[TD="class: xl67"]Full Back (Right)[/TD]

[TD="class: xl67"]Central Defender[/TD]

[TD="class: xl67"]Defensive Centre Back[/TD]

[TD="class: xl67"]Full Back (Left)[/TD]

[TD="class: xl65"] [/TD]

[TD="class: xl67"]Advanced Playmaker[/TD]

[TD="class: xl67"]Advanced Playmaker[/TD]

[TD="class: xl65"] [/TD]

[TD="class: xl67"]Inside Forward (Right)[/TD]

[TD="class: xl67"]Attacking Midfielder[/TD]

[TD="class: xl67"]Inside Forward (Left)[/TD]

[TD="class: xl65"] [/TD]

[TD="class: xl68"]Advanced Forward[/TD]

</tbody>

 

[Nine Zero]

try this out

assuming B2 is where you have the word "Central Defender" next to the 62.06

and also assuming your table is from A3 to B30

=IF(NOT(ISBLANK(VLOOKUP(B2,A3:B20,2,0))),"Taken","Available")

 

[Steviefiege]

BRILLIANT!!!!! You have no idea how long i was trying to figure that at for!!

 

[Nine Zero]

Glad I could Help

 

[Steviefiege]

Can you help me with the next part please too. Adding to that formula you gave me, i need it so as well as the position to say "Taken" for the player to say "Taken" too as i have some players who play in more than one position but if there already in the team i dont want it putting them in another position too.

 

[Nine Zero]

can you provide an example. just like you did above?

 

[Steviefiege]

It's ok, i've managed to sort that one, that was just a VLOOKUP i needed. Do need help on this one though...

Below are all my players, positions & ratings. At the side of the rating there are another 2 columns with formula's in. The next to the rating is the one you kindly did which says taken once the position is filled on the teamsheet, the one next to that says PKD to say the player is on the teamsheet

[TABLE="width: 473"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Butler, Andy[/TD]
[TD]Central Defender
[/TD]
[TD]62.06[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Butler, Andy[/TD]
[TD]Defensive Centre Back[/TD]
[TD]61.20[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Andrew, Danny[/TD]
[TD]Central Defender[/TD]
[TD]61.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Lawlor, Ian[/TD]
[TD]Goalkeeper[/TD]
[TD]60.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Rowe, Tommy[/TD]
[TD]Full Back (Left)[/TD]
[TD]59.50[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Coppinger, James[/TD]
[TD]Attacking Midfielder[/TD]
[TD]59.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Blair, Matty[/TD]
[TD]Full Back (Right)[/TD]
[TD]58.50[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Rowe, Tommy[/TD]
[TD]Inside Forward (Left)[/TD]
[TD]58.10[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Alcock, Craig[/TD]
[TD]Full Back (Right)[/TD]
[TD]58.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Marquis, John[/TD]
[TD]Advanced Forward[/TD]
[TD]57.38[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Coppinger, James[/TD]
[TD]Advanced Playmaker[/TD]
[TD]57.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Coppinger, James[/TD]
[TD]Advanced Playmaker[/TD]
[TD]57.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Rowe, Tommy[/TD]
[TD]Advanced Playmaker[/TD]
[TD]56.67[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mason, Niall[/TD]
[TD]Full Back (Right)[/TD]
[TD]56.50[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mason, Niall[/TD]
[TD]Full Back (Right)[/TD]
[TD]56.50[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mason, Niall[/TD]
[TD]Full Back (Right)[/TD]
[TD]56.50[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Alcock, Craig[/TD]
[TD]Central Defender[/TD]
[TD]56.47[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Alcock, Craig[/TD]
[TD]Central Defender[/TD]
[TD]56.47[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Alcock, Craig[/TD]
[TD]Defensive Centre Back[/TD]
[TD]55.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Alcock, Craig[/TD]
[TD]Defensive Centre Back[/TD]
[TD]55.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Kongolo, Rodney[/TD]
[TD]Advanced Playmaker[/TD]
[TD]54.17[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Whiteman, Ben[/TD]
[TD]Advanced Playmaker[/TD]
[TD]54.09[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Williams, Andrew[/TD]
[TD]Advanced Forward[/TD]
[TD]53.33[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Wright, Joe[/TD]
[TD]Central Defender[/TD]
[TD]53.24[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]May, Alfie[/TD]
[TD]Inside Forward (Left)[/TD]
[TD]53.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]May, Alfie[/TD]
[TD]Inside Forward (Left)[/TD]
[TD]53.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Kongolo, Rodney[/TD]
[TD]Central Defender[/TD]
[TD]52.94[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Whiteman, Ben[/TD]
[TD]Central Defender[/TD]
[TD]52.06[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Wright, Joe[/TD]
[TD]Defensive Centre Back[/TD]
[TD]52.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]May, Alfie[/TD]
[TD]Advanced Forward[/TD]
[TD]51.67[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Coppinger, James[/TD]
[TD]Inside Forward (Right)[/TD]
[TD]51.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Coppinger, James[/TD]
[TD]Inside Forward (Right)[/TD]
[TD]51.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Coppinger, James[/TD]
[TD]Inside Forward (Right)[/TD]
[TD]51.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Coppinger, James[/TD]
[TD]Inside Forward (Right)[/TD]
[TD]51.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Coppinger, James[/TD]
[TD]Inside Forward (Right)[/TD]
[TD]51.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Anderson, Tom[/TD]
[TD]Central Defender[/TD]
[TD]50.88[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Wright, Joe[/TD]
[TD]Full Back (Right)[/TD]
[TD]49.50[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Anderson, Tom[/TD]
[TD]Defensive Centre Back[/TD]
[TD]49.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mason, Niall[/TD]
[TD]Central Defender[/TD]
[TD]48.24[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Beestin, Alfie[/TD]
[TD]Attacking Midfielder[/TD]
[TD]48.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Beestin, Alfie[/TD]
[TD]Inside Forward (Left)[/TD]
[TD]47.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Beestin, Alfie[/TD]
[TD]Inside Forward (Left)[/TD]
[TD]47.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Beestin, Alfie[/TD]
[TD]Inside Forward (Left)[/TD]
[TD]47.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Beestin, Alfie[/TD]
[TD]Advanced Playmaker[/TD]
[TD]45.50[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Garrett, Tyler
[/TD]
[TD]Full Back (Left)[/TD]
[TD]45.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Ben Khemis, Issam
[/TD]
[TD]Advanced Playmaker[/TD]
[TD]41.00[/TD]
[TD] [/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

What i would like now is for the positions to be filled in below in my teamsheet without having to put them in manually. For example Butler, Andy will go in the Central Defender slot, then, in the above table, all players in that position will be "Taken" & Butler, Andy will say "PKD"

Example 2 would be, the next available player which is Lawlor, Ian, because the one above him, the position is taken & the one above that the player is taken

[TABLE="width: 240"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Goalkeeper
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Full Back (Right)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Central Defender[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Defensive Centre Back[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Full Back (Left)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Advanced Playmaker
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Advanced Playmaker[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Inside Forward (Right)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Attacking Midfielder[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Inside Forward (Left)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Advanced Forward
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hope you manage to find a way.

Steve