VLOOKUP formula only partly working

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:

Alex KiwomyaINJ
Alfie Beestin CM46.67 Carrilero45.50Taken
Alfie May CF51.67Yes
Ali Suljic WB (Right)41.50 BPD40.29Taken
Andrew WilliamsINJ
Andy Boyle BPD64.71Yes
Andy Butler BPD62.06Yes
Ben Whiteman Carrilero57.50Yes
Branden Horton BPD30.00
Cameron Barnett CF33.81
Carlton Cole CF57.14Yes
Cedric EvinaLOANED OUT
Craig Alcock WB (Right)58.00 BPD56.47Taken
Danny Amos WB (Left)43.50
Danny AndrewINJ
Issam Ben Khemis Carrilero50.50 CM42.92 NOT WORKING
James Coppinger CM57.92Yes
Joe Wright BPD53.24 WB (Right)49.50 NOT WORKING
John Marquis CF57.38Yes
Jordan HoughtonINJ
Liam MandevilleLOANED OUT
Luke McCulloughINJ
Mathieu BaudryINJ
Matty Blair WB (Right)58.50Yes
Mitchell LundLOANED OUT
Myron Gibbons CF35.24
Niall Mason WB (Right)56.50 WB (Right)56.50 NOT WORKING
Reece Fielding BPD48.82
Rieves Boocock CF34.29
Rodney KongoloINJ
Shane Blaney WB (Right)48.00 BPD47.35Taken
Tom Anderson BPD50.88
Tommy Rowe WB (Left)59.50Yes
Tyler Walker WB (Right)38.00
Will Longbottom CF46.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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,026
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,287
Office Version
  1. 365
Platform
  1. Windows
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,287
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,615
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Try
Code:
=IF(SUMPRODUCT(--($I$5:$I$39=M6),--($K$5:$K$39="yes"))>0,"Taken","")
 

Steviefiege

Board Regular
Joined
Aug 29, 2018
Messages
66

ADVERTISEMENT

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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,287
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Steviefiege

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

Watch MrExcel Video

Forum statistics

Threads
1,109,341
Messages
5,528,146
Members
409,802
Latest member
joeino

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top