VLOOKUP formula only partly working

[Steviefiege]

Hi,

This is my vlookup formula: =IF(M5="","",(IF(VLOOKUP(M5,$I$5:$K$39,3,FALSE)="YES","Taken",""))) but its only partly working. Below is my table:

Alex Kiwomya	INJ
Alfie Beestin			CM	46.67			Carrilero	45.50	Taken
Alfie May			CF	51.67	Yes
Ali Suljic			WB (Right)	41.50			BPD	40.29	Taken
Andrew Williams	INJ
Andy Boyle			BPD	64.71	Yes
Andy Butler			BPD	62.06	Yes
Ben Whiteman			Carrilero	57.50	Yes
Branden Horton			BPD	30.00
Cameron Barnett			CF	33.81
Carlton Cole			CF	57.14	Yes
Cedric Evina	LOANED OUT
Craig Alcock			WB (Right)	58.00			BPD	56.47	Taken
Danny Amos			WB (Left)	43.50
Danny Andrew	INJ
Issam Ben Khemis			Carrilero	50.50			CM	42.92	NOT WORKING
James Coppinger			CM	57.92	Yes
Joe Wright			BPD	53.24			WB (Right)	49.50	NOT WORKING
John Marquis			CF	57.38	Yes
Jordan Houghton	INJ
Liam Mandeville	LOANED OUT
Luke McCullough	INJ
Mathieu Baudry	INJ
Matty Blair			WB (Right)	58.50	Yes
Mitchell Lund	LOANED OUT
Myron Gibbons			CF	35.24
Niall Mason			WB (Right)	56.50			WB (Right)	56.50	NOT WORKING
Reece Fielding			BPD	48.82
Rieves Boocock			CF	34.29
Rodney Kongolo	INJ
Shane Blaney			WB (Right)	48.00			BPD	47.35	Taken
Tom Anderson			BPD	50.88
Tommy Rowe			WB (Left)	59.50	Yes
Tyler Walker			WB (Right)	38.00
Will Longbottom			CF	46.67

<colgroup><col style="mso-width-source:userset;mso-width-alt:7314;width:150pt" width="200"> <col style="mso-width-source:userset;mso-width-alt:3291;width:68pt" width="90"> <col style="mso-width-source:userset;mso-width-alt:365;width:8pt" width="10"> <col style="mso-width-source:userset;mso-width-alt:3803;width:78pt" width="104"> <col style="mso-width-source:userset;mso-width-alt:2157; width:44pt" width="59" span="2"> <col style="mso-width-source:userset;mso-width-alt:365;width:8pt" width="10"> <col style="mso-width-source:userset;mso-width-alt:2669;width:55pt" width="73"> <col style="mso-width-source:userset;mso-width-alt:2194;width:45pt" width="60"> <col style="mso-width-source:userset;mso-width-alt:2157;width:44pt" width="59"> </colgroup><tbody>
</tbody>

The ones i've put in bold down the side are the ones that arent working. Its baffling me why. They should say "Taken" but there just blank. Can anyone help please?

Rgs
Steve

 

[Peter_SSs]

Actually Scott, that formula did & didnt work. It said taken which i wanted it to say, but i also want it to say "yes" if on the first column no one is in that position

Small sample data and expected results to demonstrate that situation?

Is it this?

=IF(M5="","",(IF(COUNTIFS(I$5:I$39,M5,K$5:K$39,"Yes"),"Taken","Yes")))

 

[Steviefiege]

I've tweaked it a little bit now, in my formula: =IF(M5="","",(IF(VLOOKUP(M5,$I$5:$K$39,3,FALSE)="YES","Taken","")))
Where i've made it bold, instead of it saying yes, how do i make it so its any word? What do i put in instead of yes

 

[Steviefiege]

Thats its Peter, thats worked a treat, thank you.

 

[Peter_SSs]

Thats its Peter, thats worked a treat, thank you.

You're welcome.

I've tweaked it a little bit now, in my formula: =IF(M5="","",(IF(VLOOKUP(M5,$I$5:$K$39,3,FALSE)="YES","Taken","")))
Where i've made it bold, instead of it saying yes, how do i make it so its any word? What do i put in instead of yes

If you are still looking for a way to count anything in place of the bold YES, try

=IF(M5="","",(IF(COUNTIFS(I$5:I$39,M5,K$5:K$39,"<>"),"Taken","Yes")))

 

[Steviefiege]

Got another question Peter,

In my table below:
In the last column where it says POS TAKEN in bold, how do i put 2 VLOOKUP formula's in? Similar to the last one you did, but instead of just checking the first POS TAKEN like you just did, it needs to check the 2nd one as well.

PLAYER

SQUAD INFO

BEST POSITION

RATING

POS TAKEN

2ND BEST POSITION

RATING

POS TAKEN

3RD BEST POSITION

RATING

POS TAKEN

Alex Kiwomya

INJ

Alfie Beestin

INJ

BWM

45.00

Alfie May

CF

51.67

Yes

Ali Suljic

WB (Right)

41.50

BPD

40.29

Taken

Andrew Williams

INJ

Andy Boyle

BPD

64.71

Yes

Andy Butler

BPD

62.06

Yes

Ben Whiteman

Carrilero

57.50

Yes

BWM

54.09

Branden Horton

BPD

30.00

Cameron Barnett

CF

33.81

Carlton Cole

CF

57.14

Yes

Cedric Evina

LOANED OUT

Craig Alcock

WB (Right)

58.00

BPD

56.47

Taken

Danny Amos

WB (Left)

43.50

Danny Andrew

INJ

Issam Ben Khemis

Carrilero

50.50

CM

42.92

Yes

BWM

40.45

James Coppinger

INJ

BWM

53.64

Joe Wright

BPD

53.24

WB (Right)

49.50

Taken

John Marquis

CF

57.38

Yes

Jordan Houghton

INJ

CM

54.17

Liam Mandeville

LOANED OUT

Luke McCullough

INJ

WB (Right)

56.00

Mathieu Baudry

INJ

Matty Blair

WB (Right)

58.50

Yes

Mitchell Lund

LOANED OUT

Myron Gibbons

CF

35.24

Niall Mason

WB (Right)

56.50

WB (Right)

56.50

Taken

BWM

54.09

Reece Fielding

BPD

48.82

Rieves Boocock

CF

34.29

Rodney Kongolo

INJ

CM

54.17

Shane Blaney

WB (Right)

48.00

BPD

47.35

Taken

Tom Anderson

BPD

50.88

Tommy Rowe

WB (Left)

59.50

Yes

CM

56.67

Tyler Walker

WB (Right)

38.00

Will Longbottom

CF

46.67

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

 

[Peter_SSs]

What are the expected results and why?
Remember that I am not familiar with what your data is about and just what you are trying to do.

 

[Steviefiege]

For example: Under BEST POSITION, there is no one playing BWM, under 2ND BEST POSITION there is no one playing BWM, under 3RD BEST POSITION there is one player who so far hasn't got a yes next to his name at all, NIALL MASON, in POS TAKEN at the end i want it to say "yes" in there & then in all the other ones to say "taken"

 

[Peter_SSs]

The lookups appear to have grown from 1 to 2 to now 3. Is that all there will be?

In any case, I still don't fully understand and I have no idea what row you are talking about in post 17, so why not show all the expected results for all the rows and provide any further clarification you can.
It would also be helpful to know what columns and what rows we are looking at (like you can see in post 5). My signature block below has some help about that.