You Got The Vlookup

If you do the Twitter thing, follow me at @hotpixUK
or LinkedIn here uk.linkedin.com/pub/tony-smith-bsc-hons-acih/a/979/351/

Wow, I have often joked that if I had charged everyone over the past 20 years £50 for every VLOOKUP problem in Excel I have ever solved, I would have my pension, right there. It would be a vast sum. However the easy going nature I have does not permit me to be anything else than helpful and sympathetic, whenever anyone asks me to solve one.

For a customer I have been doing lots of Excel manipulations lately and from the mark its left on me here is my contribution to VLOOKUP peace in the world.

Excel is great and VLOOKUP / HLOOKUP are in the premier league of functions to be mastered. Also probably the cause of many people giving up functions forever. Well, that's sad and if that's you, please I implore you, read on....

In this article I use both VLOOKUP and HLOOKUP interchangeably.

It seems simple, here's one I wrestled with earlier:
       =VLOOKUP(A2,Sheet2!C1:F10,22, TRUE)

So, the first hurdle is not going with the default, 'TRUE'. Spandau Barry tried to persuade us things could be 'True', but here it means 'find the best match you can'. OK if that's what you want, but on the whole it wont be. False, means do a direct match. Swap it now.

Check your values (either the one you are trying to match OR the area you are trying to find it) dont suffer from trailing spaces. You wont see them in the spreadsheet, only when you edit a cell and notice the cursor is not exactly at the end of your value to search. Use Trim() to remove any excess space characters.

Leading zeros in text formatted cells may also cause you problems. IE '009089' will not match as '9089'. This can happen a lot where data such as rent account IDs are infact textual, but look like numbers (ie having leading zeroes). Try multiplying by 1 or adding 0 to these values before searching OR within the first parameter of the VLOOKUP function. EG 'VLOOKUP( C2 + 0, ' or 'VLOOKUP( C2 * 1 , ' etc.  This will turn '009089' into '9089'.

Often people get one VLOOKUP working in a single cell, only to copy and find it only works some of the time. Well, fix your columns or ranges, using $. E.G. 
       =VLOOKUP(A2,Sheet2!$C$1:$F$10,22, TRUE)
So that when copied, the whole matrix searched is unchanged, when copied. This is sometimes called 'Reference Locking'.

The other 'Gotcha' is to define a range and then try to return a column to the left of the defined Table Array range. In this example, a range takes in columns C to F. Column 22 as a return value will never work. Extend the range to the right (ie C to X), in order to pick up the correct values.

Still getting #N/A, but quite obviously you can see a match?  I bet you are trying to locate a number. Have you considered utilising TRUNC( ?? , 0). Excel often will try to be clever and compare 6.7 with 6.700 and decide the values are not the same. Having numbers, that are actually just textual IDs, formatted as Text makes sense.

Excel macro's can also be problematic when adding VLOOKUPs to child links, having child links open first, makes a macro work better.

I hope this helps, you have cost my pension £50 chum, but I don't mind, I am doing my bit for world peace....

Related Post: Are your staff on top of Asset Sustainability & Living Wills? How do you start?

_/_/_/_/_/_/_/_/

You can link with me on LinkedIn here - uk.linkedin.com/pub/tony-smith-bsc-hons-acih/a/979/351/ It would be great to connect !

Prince - You Got The (v)Look(up) .


(c) Tony Smith, Acutance Consulting www.acutanceconsulting.co.uk 07854-655009

PS As usual, if there are subjects you might like me to tackle on this blog, please get in touch and let me know!

File Under: 360,1stTouch,4Js. 07854-655009 ,7854655009, 07854 655009 ," ""07854 655009"" ",07854-655009,#UKHousing,1st Touch,3squared,24Dash,Access Financials,Acutence,Aareon,Academy,ActiveH,Alignment,ALMO,Anite,Apex,ArchHouse,Archouse,asbestos,Asprey e-state pro,Asset Management,Aurora,Average IT Costs,App,Associates,ACL,Abritas,AMS,AX,Aspireview,ACS,Advanced,Agresso,Allpay,Auto assessor,Ablyss CMS,Adastra,Advanced Community,Accuserve,Adobe Echosign,Actionfile,BO,BPR,Browser Applications,Business Objects,Business Process Review,Business social networking, Blockwise, Block wise,BI,BancTec,BluTek,Bluebox,Blueprint,BPM, Castle,CBL,Cedar Open Accounts,Change,Cheaper Housing IT,Chics, CHR,Citrix,Civica,Clearview,CMS , CCS IT Keystone CCSIT, Contractor Systems,CORE,CorVu,Cost Reductions,Covalent,CRM,Crystal Reports,CTI,CTX,Customer Relationship Management,Cashflow, COA,Coactiva,Codeman,Comino,Competitive Dialogue process,complex IT procurements,Component Accounting,Consilium,Consolidation,Consultancy,Consultant,Contact Manager,Context,CIH,Chartered Institute Of Housing,Cadcorp,Cerrus Saturn,convergeOne,CACI,CPL,Cx,Cascade,CSI,Civica Cx,Care and Support,Carenotes,CareSys,CDPSharp,Crosscare,Contract Management,Contact Management,Clinical,Conversions,COINS,Click,Clicksoftware Scheduling,Director,Deeplake,Development Systems,Document Management,Documotive,datasystems ,Dinamiks,DRS,Drupal,ECMK,EDRMS,England, English,EnterpriseBI,ERP Systems,ERP,Elmhurst,Estatecraft,EDM,ESRI,Exponential-e,Epicor,Exel,EOLC,escrow,Finance,Financial Systems,Financials, Factorwise,Facebook ,Fusion,Frontline,Getting best from,Grasp,Grip,GeoSolveIT,GIS,GGP,gotonysmith,Gentoo Streetwise,Guardian24,G-Cloud,Goldcrest,GeoSolveIT,HouSys,Housing software,HG,Housemark survey,Housing Blueprint,Housing Group,hotpixuk, HousingIT,hotpix,Hardware,Hitex,Housing Contact Company,HAMMAR assets repairs forum,HomeHunt,Helpdesk software,HouseOnTheHill,Impact Response,implementations,In House,In4,Infoflow,Information Technology,Informix,inHouse,in-house workforce, Innovation, Inside Housing,Internet Portal,Invu,Ireland,Irish,IT Budget,IT Training,iWorld, Innovation Group Apex,ITIL, In4Systems Promaster,IT,Insight,iCaseWork,iCareHealth,iConnect,iPad,iNurse,InterNETalia,InMotion,InPhase,Joomla,Keylogic,Keystone,Kirona,Kypera,Keyfax,Kofax,Kronos,Ledgers,Linkedin,Locality,Liquid Voice,Lakewood,MD ,Mr Void , MrVoid, Measuring Change,Mebus,Miracle,MISCS, mobile functionality,Monopoly board images and pictures, Montal,MS Dynamics,MS Dynamics CRM2011,Miracle Software,Management Reporting,MI,M3,MIS,MIS-AMS,Mobysoft,Manual,MWL Mavis,Metastorm,Module,Northgate Codeman,Neighbourhoods and Communities, Northgate,Notice,NROSH,NINTEX , NINTEX workflow,Nintexworkflow,NHF,National Housing Federation,National Energy Services,NES,North West Online,NCC,Netcall,OA,OGC Buying Solutions,Ohms,OJEU Limits,OmFax,Omniledger,Open source software,open tender,OpenHousing,Opti-Time,Oracle,Orchard,Outsource and outsourcing,OpenContractor,ORS,OneServe,OpenFinancials,OpenTouch,OSKA,octavia,Odyssey,OpenText, PIMMS Data Systems, Paloma,performance management systems,PfH,Pick,PIMMS,pimms4communities,Planned maintenance,Plus,PM,Progress,Promaster,Proval,Pyramid,PRINCE2,prince,Pamwin,People Value,Peoplevalue,PocketSurvey,Pocket Survey,Pathfinder,Pathfinder RF,Project Management,QL,QLX,Quiss,Qlikview,Registered Providers,Registered Social Landlord,Rent Increase Freezes,Repairfinder,Reporting,Reports,ROCC , Rocket,RPs,RSL,Reality,ReAct,reidmark,RM865,RAM,Real Asset Management,Residata,Rostering,Rootmetrics,Rocket CorVu,Real estate management,Saffron,SAP,Scots, Scottish,Scotland,SDM,sector,Server Virtualisation,servicing, Servitor,Sharepoint,Simdel,Simdell,Slash and Burn,Social Media,Software,SQL Open Housing,SQL Reporting Services,SQL reporting services,SQL Server,Star rating,Stores and Stock,Strategic Asset Management,Sunguard ,Surveys,Sx3,System alignment, Systemwise,SM,Sequoa,Serros,SP,Scout,score,Swordfish,Serengeti,SOTI,south view,Sunguard,Service Charges,Streetwise,Scout Solutions,Strategic Asset Management, Social Housing software & IT-based housing management solutions for social housing providers social housing,management services,software,solutions,social, housing,managed services,managed,business,housing management,solutions , SPOCC,Sprint,Staffplan Roster,Sovereign,SDS,Solarwinds Web Helpdesk,Template,Task,Terminal Services, tonys , tonysm , tonysmi , tonysmith, tonysmithth, tonysmiththa, tonysmiththat, tonysmiththath, tonysmiththatho, tonysmiththathou, tonysmiththathous, tonysmiththathousi, tonysmiththathousin, tonysmiththathousing, tonysmiththathousingg, tonysmiththathousinguy, sharepoint, share point Three Star,Today,Total repairs,Tribal,Twitter,U2, tonysmith , tonysmiththat , thathousing , thathousingIT , thathousingITguy ,TED,Tagish Casework,TP Tracker,Telecetera,Tagra,Trace,TM,TotalMobile,Total Mobile,TPTracker,TapForms,TagTronics,TagtronicsCare,TMWK,UniClass Enterprise,Unidata,Universal Housing,Universalcredit, universal credit, universal credits,UC,Universe,Unrest, UK, United Kingdom ,Ukhousing,UK Housing,U.K. ,Van Stock,Voice and data,Vantage Sentinel,Version One,Visualmetrics,Voluntas,Vanguard John Seddon,Volunteer Module,Volunteers,Vebra,Vixen,VerseOne,Wales,Welsh,Windows Server,Workflow and tasking,Wheatley,Web,housing management solution, Xmbrace,XML ,XenApp,Xen App,Zendesk ,.