Vlookup 11

[EliseFreedman]

Can anyone tell me what is wrong with my vlookup formula. Its not returning the results i expect.

Basically I have 2 spreadsheets within a single workbook

On my main spreadsheet I have a group of cells for entering a persons job description.

e.g F H
10 JOB CODE JOB DESCRIPTION
11 IWT IWT Manager
12 FA First Aid Technician

The idea is that in Column H there is a vlookup formula which uses a separate spreadsheet to look up the job descriptions corresponding to the Job Code that the User has entered. My Formula is as follows

<code>
=VLOOKUP(F10,JobsDatabase!$A$1:$B$6,2)

where JobsDatabase is the name of the 2nd spreadsheet as shown

Job Code Description
FA First Aid Technician
IWT IWT Manager
HS HS&E Manager
SS Safety Supervisor

My Formula seems to be returning very odd results and I just cant figure out where im going wrong e.g If I Type in SS or IWT then it comes up with the correct job description. However if I attempt to select FA or HS then i just get #N/A even though FA and HS are definately included in the table. Can anyone point out where Im going wrong?

 

[xlhelp]

Try adding a False at the end

=VLOOKUP(F10,JobsDatabase!$A$1:$B$6,2,False)


Me transmitte sursum, Caledoni!

 

[kjv1611]

EliseFreedman,

If "Job Code" and "Description" are on Row 1, I would suggest setting your formula to:

=VLOOKUP(F10,JobsDatabase!$A$[b]2[/b]:$B$6,2)

Also, have you tried using WildCards in your formulas to handle any extra spaces that might end up getting typed on accident? I'm not sure right off the top of my head how you would do this with VLOOKUP, but I'm sure there's a way to do so.

 

[firefytr]

Yes, VLOOKUP can take wildcards (not all formulas can)...

=VLOOKUP("*"&F10&"*",JobsDatabase!$A$1:$B$6,2,0)

-----------
Regards,
Zack Barresse

 

[xlbo]

Zack - I consider myself very experienced with VLOOKUP but I never knew that !!!

That's really useful - have a *

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

Well I'm honored to have shown you something Geoff! Maybe I can start repaying you for what you've given me! :-D

-----------
Regards,
Zack Barresse

 

[xlhelp]

Zack, Me 2.

I have been teaching spreadsheets for *umpteen* years and did not know that. Thanks.

Me transmitte sursum, Caledoni!

 

[KenWright]

The wildcard can be used in various ways within VLOOKUP depending upon need:-

=VLOOKUP("*"&C1&"*",A1:B100,2,0)
=VLOOKUP(C1&"*",A1:B100,2,0)
=VLOOKUP("*"&C1,A1:B100,2,0)

=VLOOKUP("*def*",A1:B100,2,0)
=VLOOKUP("def*",A1:B100,2,0)
=VLOOKUP("*def",A1:B100,2,0)

Also, if the issue was rogue spaces, then another possible solution would be to use the TRIM function within the VLOOKUP formula and then array enter it using CTRL+SHIFT+ENTER:-

=VLOOKUP(F10,TRIM(JobsDatabase!$A$2:$B$6),2)

In this instance though, the wildcard is probably more efficient than the array.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Just came across an old post of mine where I had listed a number of different examples of using wildcards, so in case this is of any use to anyone:


They can be used in various formulas but not all functions will support them. Another option that is similar is to use FIND or SEARCH within a SUMPRODUCT function, which will mimic a wildcard search.

=SUMPRODUCT(ISNUMBER(SEARCH("bcd",$A$2:$A$30))*$B$2:$B$30)

Other misc examples

=SUMIF(P25,"<>*LLC",DB2B5)
=COUNTIF(data,"SEC*")
=IF(COUNTIF(A2:A2,"?abc"),"yes","no")
=IF(COUNTIF(A2:A2,"*abc"),"yes","no")
=IF(COUNTIF(A2:A2,"*abc*"),"yes","no")
=IF(ISNUMBER(FIND("abc",A2)),"yes","no")
=IF(ISNUMBER(FIND("abc",A2)),"yes","no")
=COUNTIF(A1:A5,"*a*")
=COUNTIF(A1:A5,"*a")
=COUNTIF(A1:A5,"a*")
=COUNTIF(A1:A5,"a?cd")
=AVERAGE(IF(ISNUMBER(SEARCH("green",A1:A10)),B1:B10))
=COUNTIF(A:A,"????t*")
=SUMPRODUCT((F3:F5000="1c33")*ISNUMBER(SEARCH("3w15?",I3:I5000)))
=VLOOKUP("*"&A1&"*",Sheet2!A:B,2, FALSE)

Regards
Ken...............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[firefytr]

Nice examples Ken.

I would mention about formulas where it would be best to get the source problem. In the case of the formula =VLOOKUP(F10,TRIM(JobsDatabase!$A$2:$B$6),2) it would be better to take care of the leading/trailing spaces at the source data; just making the distinction.

Take care!

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication.
- Leonardo da Vinci

 

[KenWright]

100% agreed. I'll cleanse the data every time and fix the problem at source rather than try and simply cur ethe symptoms. Only way to be sure you catch it all

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Gavona]

Really useful tips. Thank you both.


Gavin

 

[Bowers74]

I don't come here often, but when I do I am amazed to see how much more I need to learn before I can know as much as I thought I already knew (if that even makes sense).

Kodos 2 u both!! and *'s all around!!!



Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here

 

[rvuxta]

Elise,

I think we might've got off the track here. The reason that your getting strange returns from your formula is because vlookup requires that your 2nd database be in sorted order. The sort key has to be the column that your looking for - in your example, the 2nd database should be sorted on Job Code.

Hope this helps,

Ron

 

[AnotherHiggins]

Ron,

If you last argument is FALSE (or zero) (as suggested by xlhelp in the first response in this thread), you do not need to have the table sorted. Using False will only return exact matches.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[KenWright]

And if you (Ron) look at the examples posted, they are almost all inclusive of that 4th argument, because most times (for me anyway), I usually do want an exact match.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------