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

Butler, Andy

Central Defender

62.06

<colgroup><col width="124"><col width="157"><col width="64" span="2"></colgroup><tbody>
</tbody>

Goalkeeper
Full Back (Right)
Central Defender	Boyle, Andy
Defensive Centre Back
Full Back (Left)
Advanced Playmaker
Advanced Playmaker
Inside Forward (Right)
Attacking Midfielder
Inside Forward (Left)	Rowe, Tommy
Advanced Forward	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>
</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

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

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

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

Goalkeeper
Full Back (Right)
Central Defender
Defensive Centre Back
Full Back (Left)
Advanced Playmaker
Advanced Playmaker
Inside Forward (Right)
Attacking Midfielder
Inside Forward (Left)
Advanced Forward

<colgroup><col><col></colgroup><tbody>
</tbody>

Hope you manage to find a way.

Steve