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

 

[jmacleary]

Hi there

If you look at your data, there are more than 1 entries for the ones that aren't working. The vlookup is matching on the first occurrence which doesn't have a Yes against it.

John

 

[Steviefiege]

What do i do to solve it?

 

[Peter_SSs]

Why should they be "Taken"?
When the VLOOKUP does its thing, it uses the first occurrence of the lookup value that it finds in the left column. As you can see below, those vlookups do not find "Yes" in column K, hence the results you get.

Excel Workbook
	I	J	K	L	M	N	O
5
6	CM	46.67			Carrilero	45.5	Taken
7	CF	51.67	Yes
8	WB (Right)	41.5			BPD	40.29	Taken
9
10	BPD	64.71	Yes
11	BPD	62.06	Yes
12	Carrilero	57.5	Yes
13	BPD	30
14	CF	33.81
15	CF	57.14	Yes
16
17	WB (Right)	58			BPD	56.47	Taken
18	WB (Left)	43.5
19
20	Carrilero	50.5			CM	42.92
21	CM	57.92	Yes
22	BPD	53.24			WB (Right)	49.5
23	CF	57.38	Yes
24
25
26
27
28	WB (Right)	58.5	Yes
29
30	CF	35.24
31	WB (Right)	56.5			WB (Right)	56.5
32	BPD	48.82
33	CF	34.29
34
35	WB (Right)	48			BPD	47.35	Taken
36	BPD	50.88
37	WB (Left)	59.5	Yes
38	WB (Right)	38
39	CF	46.67
Lookup

 

[Peter_SSs]

Sorry, I had missed the previous couple of posts while I was composing mine.

What do i do to solve it?

Depends what your requirements are, but perhaps this?

Excel Workbook
	I	J	K	L	M	N	O
5
6	CM	46.67			Carrilero	45.5	Taken
7	CF	51.67	Yes
8	WB (Right)	41.5			BPD	40.29	Taken
9
10	BPD	64.71	Yes
11	BPD	62.06	Yes
12	Carrilero	57.5	Yes
13	BPD	30
14	CF	33.81
15	CF	57.14	Yes
16
17	WB (Right)	58			BPD	56.47	Taken
18	WB (Left)	43.5
19
20	Carrilero	50.5			CM	42.92	Taken
21	CM	57.92	Yes
22	BPD	53.24			WB (Right)	49.5	Taken
23	CF	57.38	Yes
24
25
26
27
28	WB (Right)	58.5	Yes
29
30	CF	35.24
31	WB (Right)	56.5			WB (Right)	56.5	Taken
32	BPD	48.82
33	CF	34.29
34
35	WB (Right)	48			BPD	47.35	Taken
36	BPD	50.88
37	WB (Left)	59.5	Yes
38	WB (Right)	38
39	CF	46.67
Lookup

 

[Scott T]

Try

=IF(SUMPRODUCT(--($I$5:$I$39=M6),--($K$5:$K$39="yes"))>0,"Taken","")

 

[Steviefiege]

Right, get that now. Would it be better to use a match formula instead then?
The reason why i want them to say taken is because if you look at the sheet i put on, for example the CM role, James Coppinger is playing in that position so i want everyone one else who plays in CM to say taken as the position is taken

 

[Steviefiege]

That formula works a treat, thank you Scott.

 

[Peter_SSs]

That formula works a treat, thank you Scott.

It may not be possible with your data but that will give some I think unexpected results if any of the rows that are blank in column I happen to have a "Yes" in column K. The original check on column M that you had (IF(M5="", "", ...), and repeated in my suggestion in post 5 would avoid that possibility corrupting your results.

 

[Steviefiege]

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