VLOOKUP formula only partly working

S

Steviefiege

Board Regular
Joined
Aug 29, 2018
Messages
66
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:


<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>
[TD="class: xl66, width: 200"]Alex Kiwomya[/TD]
[TD="class: xl67, width: 90"]INJ[/TD]
[TD="class: xl64, width: 10"] [/TD]
[TD="class: xl67, width: 104"] [/TD]
[TD="class: xl68, width: 59"] [/TD]
[TD="class: xl68, width: 59"] [/TD]
[TD="class: xl65, width: 10"][/TD]
[TD="class: xl67, width: 73"] [/TD]
[TD="class: xl70, width: 60"] [/TD]
[TD="class: xl68, width: 59"] [/TD]

[TD="class: xl66, width: 200"]Alfie Beestin[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]CM[/TD]
[TD="class: xl68"]46.67[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]Carrilero[/TD]
[TD="class: xl70"]45.50[/TD]
[TD="class: xl68"]Taken[/TD]

[TD="class: xl66, width: 200"]Alfie May[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]CF[/TD]
[TD="class: xl68"]51.67[/TD]
[TD="class: xl68"]Yes[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl66, width: 200"]Ali Suljic[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]WB (Right)[/TD]
[TD="class: xl68"]41.50[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]BPD[/TD]
[TD="class: xl70"]40.29[/TD]
[TD="class: xl68"]Taken[/TD]

[TD="class: xl66, width: 200"]Andrew Williams[/TD]
[TD="class: xl67"]INJ[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl66, width: 200"]Andy Boyle[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]BPD[/TD]
[TD="class: xl68"]64.71[/TD]
[TD="class: xl68"]Yes[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl66, width: 200"]Andy Butler[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]BPD[/TD]
[TD="class: xl68"]62.06[/TD]
[TD="class: xl68"]Yes[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl66, width: 200"]Ben Whiteman[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]Carrilero[/TD]
[TD="class: xl68"]57.50[/TD]
[TD="class: xl68"]Yes[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl66, width: 200"]Branden Horton[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]BPD[/TD]
[TD="class: xl68"]30.00[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl66, width: 200"]Cameron Barnett[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]CF[/TD]
[TD="class: xl68"]33.81[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl66, width: 200"]Carlton Cole[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]CF[/TD]
[TD="class: xl68"]57.14[/TD]
[TD="class: xl68"]Yes[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl66, width: 200"]Cedric Evina[/TD]
[TD="class: xl67"]LOANED OUT[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl66, width: 200"]Craig Alcock[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]WB (Right)[/TD]
[TD="class: xl68"]58.00[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]BPD[/TD]
[TD="class: xl70"]56.47[/TD]
[TD="class: xl68"]Taken[/TD]

[TD="class: xl66, width: 200"]Danny Amos[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]WB (Left)[/TD]
[TD="class: xl68"]43.50[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl66, width: 200"]Danny Andrew[/TD]
[TD="class: xl67"]INJ[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl66, width: 200"]Issam Ben Khemis[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]Carrilero[/TD]
[TD="class: xl68"]50.50[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]CM[/TD]
[TD="class: xl70"]42.92[/TD]
[TD="class: xl68"] NOT WORKING
[/TD]

[TD="class: xl66, width: 200"]James Coppinger[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]CM[/TD]
[TD="class: xl68"]57.92[/TD]
[TD="class: xl68"]Yes[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl66, width: 200"]Joe Wright[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]BPD[/TD]
[TD="class: xl68"]53.24[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]WB (Right)[/TD]
[TD="class: xl70"]49.50[/TD]
[TD="class: xl68"] NOT WORKING
[/TD]

[TD="class: xl66, width: 200"]John Marquis[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]CF[/TD]
[TD="class: xl68"]57.38[/TD]
[TD="class: xl68"]Yes[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"]
[/TD]

[TD="class: xl66, width: 200"]Jordan Houghton[/TD]
[TD="class: xl67"]INJ[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"]
[/TD]

[TD="class: xl66, width: 200"]Liam Mandeville[/TD]
[TD="class: xl67"]LOANED OUT[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"]
[/TD]

[TD="class: xl66, width: 200"]Luke McCullough[/TD]
[TD="class: xl67"]INJ[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"]
[/TD]

[TD="class: xl66, width: 200"]Mathieu Baudry[/TD]
[TD="class: xl67"]INJ[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"]
[/TD]

[TD="class: xl66, width: 200"]Matty Blair[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]WB (Right)[/TD]
[TD="class: xl68"]58.50[/TD]
[TD="class: xl68"]Yes[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"]
[/TD]

[TD="class: xl66, width: 200"]Mitchell Lund[/TD]
[TD="class: xl67"]LOANED OUT[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"]
[/TD]

[TD="class: xl66, width: 200"]Myron Gibbons[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]CF[/TD]
[TD="class: xl68"]35.24[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"]
[/TD]

[TD="class: xl66, width: 200"]Niall Mason[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]WB (Right)[/TD]
[TD="class: xl68"]56.50[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]WB (Right)[/TD]
[TD="class: xl70"]56.50[/TD]
[TD="class: xl68"] NOT WORKING
[/TD]

[TD="class: xl66, width: 200"]Reece Fielding[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]BPD[/TD]
[TD="class: xl68"]48.82[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"]
[/TD]

[TD="class: xl66, width: 200"]Rieves Boocock[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]CF[/TD]
[TD="class: xl68"]34.29[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl66, width: 200"]Rodney Kongolo[/TD]
[TD="class: xl67"]INJ[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl66, width: 200"]Shane Blaney[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]WB (Right)[/TD]
[TD="class: xl68"]48.00[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]BPD[/TD]
[TD="class: xl70"]47.35[/TD]
[TD="class: xl68"]Taken[/TD]

[TD="class: xl66, width: 200"]Tom Anderson[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]BPD[/TD]
[TD="class: xl68"]50.88[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl66, width: 200"]Tommy Rowe[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]WB (Left)[/TD]
[TD="class: xl68"]59.50[/TD]
[TD="class: xl68"]Yes[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl69"]Tyler Walker[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]WB (Right)[/TD]
[TD="class: xl68"]38.00[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl66, width: 200"]Will Longbottom[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67"]CF[/TD]
[TD="class: xl68"]46.67[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl68"] [/TD]

</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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
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
 
Upvote 0
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
IJKLMNO
5 
6CM46.67Carrilero45.5Taken
7CF51.67Yes
8WB (Right)41.5BPD40.29Taken
9
10BPD64.71Yes
11BPD62.06Yes
12Carrilero57.5Yes
13BPD30
14CF33.81
15CF57.14Yes
16
17WB (Right)58BPD56.47Taken
18WB (Left)43.5
19
20Carrilero50.5CM42.92
21CM57.92Yes
22BPD53.24WB (Right)49.5
23CF57.38Yes
24
25
26
27
28WB (Right)58.5Yes
29
30CF35.24
31WB (Right)56.5WB (Right)56.5
32BPD48.82
33CF34.29
34
35WB (Right)48BPD47.35Taken
36BPD50.88
37WB (Left)59.5Yes
38WB (Right)38
39CF46.67
Lookup
 
Upvote 0
Sorry, I had missed the previous couple of posts while I was composing mine. :)

Steviefiege said:
What do i do to solve it?
Click to expand...
Depends what your requirements are, but perhaps this?

Excel Workbook
IJKLMNO
5 
6CM46.67Carrilero45.5Taken
7CF51.67Yes
8WB (Right)41.5BPD40.29Taken
9
10BPD64.71Yes
11BPD62.06Yes
12Carrilero57.5Yes
13BPD30
14CF33.81
15CF57.14Yes
16
17WB (Right)58BPD56.47Taken
18WB (Left)43.5
19
20Carrilero50.5CM42.92Taken
21CM57.92Yes
22BPD53.24WB (Right)49.5Taken
23CF57.38Yes
24
25
26
27
28WB (Right)58.5Yes
29
30CF35.24
31WB (Right)56.5WB (Right)56.5Taken
32BPD48.82
33CF34.29
34
35WB (Right)48BPD47.35Taken
36BPD50.88
37WB (Left)59.5Yes
38WB (Right)38
39CF46.67
Lookup
 
Upvote 0
Try
Code: 
=IF(SUMPRODUCT(--($I$5:$I$39=M6),--($K$5:$K$39="yes"))>0,"Taken","")
 
Upvote 0
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
 
Upvote 0
Steviefiege said:
That formula works a treat, thank you Scott.
Click to expand...
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
W
  • Question Question
Return Related URLs into one string (Textjoin)
Replies
1
Views
980
WilldoSEO
W
D
  • Question Question
Formula needed. Looking a value associated with a common data attribute
Replies
5
Views
869
Fluff
Fluff
N
  • Question Question
sorting to other worksheets
Replies
6
Views
1K
NatureGreen
N
U
  • Question Question
Format the 3 numbers in a column closest to ZERO
4 5 6
Replies
51
Views
6K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top