6 Tips for Writing Better VLOOKUPs

Share

Facebook
Twitter
LinkedIn
This article is part of our VLOOKUP Week. Read more.

Ok, you have learned how to write vlookup formulas. You have also seen some pretty interesting examples of it (1, 2).

But how do you write better VLOOKUP formulas?

Write better VLOOKUP Formulas - 6 tips

Here is a list of 6 tips that work wonders with VLOOKUP writing.

  1. Use named ranges

    No one likes a VLOOKUP with lots of dollars and cell references. They are unreadable and difficult to debug. So, a better option is to use named ranges in lookup formulas. For eg: =VLOOKUP(valSalesPerson,tblData,3,FALSE) is much more easier to read and understand than =VLOOKUP(G5,$B$5:$G$17,3,FALSE)

  2. Make table / list references Absolute

    When you need to write lookup formulas in a range of cells, the usual practice is to write first formula and then drag-fill. If you followed above advice and used named ranges, you should be ok with this approach. But if you do use cell references, make sure the table references are absolute, like this: $B$5:$G$17 instead of B5:G17. Here is a nice tutorial explaining the concept of cell references.

  3. Use Tables or Lists [Excel 2003 or above only]

    Using named ranges or absolute references is good technique. But they suffer from one nagging limitation. If your source data (where you are looking up) grows or shrinks, you need to adjust the references. A better option? Use tables (or lists in 2003). Learn more about Excel Tables & how to use them.

  4. Check for Errors

    VLOOKUP is a powerful formula, alright. But ask it to look up a value that is not the data and it acts up. So you need to handle this. The easiest method is to use IFERROR() formula. Like this: =IFERROR(VLOOKUP(…),”Oops, nothing found!”).

    But, IFERROR is a new formula in Excel 2007, so if you are using 2003 or before versions you need to use ISERROR (), like this: =IF(ISERROR(VLOOKUP(…)),”Oops, nothing found!”,VLOOKUP(…))

    But, there is another problem. The ISERROR() is not only lengthy, it is also ugly and inefficient as it calculates VLOOKUP twice.  So a better approach is to use COUNTIF(), like this: =IF(COUNTIF(column, value you want to lookup)>0,VLOOKUP(…),”Oops, nothing found!”) [more: how to handle formula errors]

  5. Use 1 and 0 for last argument

    While I do not recommend this, I have seen many do this in practice. You can use 1 and 0 for last argument in VLOOKUP to make the formula shorter. The formula =VLOOKUP(value, range, column #, FALSE) is same as =VLOOKUP(value, range, column #, 0). Similarly you can use 1 for TRUE.

    What more, you can even omit the last argument if it is 0, like this: =VLOOKUP(value, range, column #, ) Remember, you must place a comma (,) after the column number if you are planning to use this.

  6. Use VLOOKUP only when you need it

    Do you know that formulas like SUMIF() or SUMPRODUCT() can effectively replace VLOOKUP() formulas? For eg. the formula =SUMIF(lookup-range, lookup value, return column range) gives same value as =VLOOKUP(lookup value, total range, 2, false) ? [assuming there is only one match, return column range has numbers]

    Similarly, if you just want to find whether a value is in a list or not, use COUNTIF() formula.

    That is right. Not only formulas like SUMIF are better, they require no separate error handling. If the value cannot be found, they just return 0.
    [Learn more about SUMIF & COUNTIF formulas]

Your suggestions for writing better VLOOKUP

What tips / ideas you follow for writing better lookup formulas? Please share using comment.s

Special Thanks to,

Vipul, Ayush Jain, Spotpuff, Glen Feechan, Dominik Petri, Lukas for their valuable tips & ideas. Click on their names to learn more on using VLOOKUP.

VLOOKUP Week @ Chandoo.org - Learn tips on lookup formulas in Excel

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

13 Responses to “Gantt Box Chart Tutorial & Template – Download and Try today”

  1. Oli says:

    Hi Chandoo

    As one of your students I have followed your detailed example through with great success. However, Excel is acting in an unexpected way and I wonder if you could take a look?
    http://cid-95d070c79aef808e.office.live.com/self.aspx/.Public/Gantt%20Box%20Chart.xlsm
    On my version, I have to type 40239 (Which equates to 2 Mar 2010) to get the chart to display 31 May 2010 (which should be 40329)!!??

    Have I done something wrong or is Excel acting up?

    Thx
    Oli
    PS Your example file in 2007 displays correctly.

  2. Dave says:

    Hi,

    I like this idea a lot, but I agree the name is a little drab.

    As an American I may just be seeing things, but to me the combination of lines and bars on your chart looks like a bunch of cricket bats.

    Maybe you could work that into a catchier name. 🙂

    Cheers!

  3. Bob says:

    Here is some code I use to keep the axis synched.
    It may be useful to some of your readers
    It is based on a comment I saw on Daily Dose of Excel.

    Function SynchGanttAxis(Cname, lower, upper)
    'Sets the X min and X max for Category axis

    Application.Volatile

    On Error Resume Next
    '
    'Top Horizontal Axis
    With ActiveSheet.Shapes(Cname).Chart.Axes(xlCategory, 1)
    .MinimumScale = lower
    .MaximumScale = upper
    End With

    'Bottom Horizontal Axis
    With ActiveSheet.Shapes(Cname).Chart.Axes(xlValue, 2)
    .MinimumScale = lower
    .MaximumScale = upper
    End With

    End Function

    Function SynchVerticalAxis(Cname, lower, upper)
    Application.Volatile
    On Error Resume Next
    ' Excel 2007 only
    'Right hand vertical axis
    With ActiveSheet.Shapes(Cname).Chart.Axes(xlValue, 1)
    .MinimumScale = 0
    .MaximumScale = upper
    End With

    End Function

  4. Chandoo says:

    @Oli.. Can you check your file again.. I see 40329...

    @Dave: Even I saw things.. the bars actually looked like lollipops. How about calling this lollipop chart - now that would be yummy and goes along the tradition of naming charts after eatables (bar, pie, donut...)

    @Bob: Superb stuff... thanks for sharing 🙂

  5. Mike H says:

    Hi Chandoo
    This looks really good and I think it can also be applied to show project phases / milestones.

    Question: Thinking further could this be amended to display a project lifecycle (Idea through to Implementation say 7 phases) on one bar / row? Just imagine 20 projects within a programme all on one chart one bar each showing their respective lifecycle stages i.e. on one page.

    Idea: As the Gantt Box Chart this is quite intensive to set up re formatting etc how about the added extra of once you have completed this to "Save as template" i.e. saves the formatting and layout of the chart as a template so you can apply to future charts. Simple to do and will save the time formatting etc again and again and again.
    Therefore tip: Click on your chart demo and then click on Save As template icon (2007) - edit file name and click on save. Ready to use / apply via Templates in Change Chart Type window.

    Thanks and be very interested if the lifecycle question can be resolved

    Mike

  6. Oli says:

    How embarrassing.

    I was obviously suffering from numerical dyslexia. I was one of those days.

  7. Chandoo says:

    @Mike H: You can easily make this chart to work like a generic project lifecycle plan chart. All you have to do is,

    1. in a separate sheet define the steps of lifecycle and various dates in a table (with 5 columns for each of the projects you have).
    2. now use a control cell to input the project name you want to show in the chart
    3. based on the input, use OFFSET Formulas to get the correct data
    4. Rest is same as the tutorial above

    For more info on the dynamic charting visit http://chandoo.org/wp/tag/dynamic-charts/ and http://chandoo.org/wp?s=OFFSET

  8. Your solution is really smart but in the en Excel isn't meant to do stuff like this. I, as a former PM, always thought is was frustrating that you had to do stuff like this for something simple like a Gantt chart. So I built Tom's Planner. And would like to plug it here. I think it really solves the problem you are trying to solve in the most efficient way. Check out http://www.tomsplanner.com for a free account or play around with the demo.

  9. Lopi says:

    Hi there,
    Chandoo - this is really a very nice and helpfull chart - I adopted it, so I can report a forecast or the delay of a certain task (coming from my role as an auditor for projects).
    One topic I´m currently struggeling with: I do have a project lasting for lets say 12 month. For a management reporting, I want to have kind of snapshot, lets say one month back and 2 month in the future. I tried with the offset formula, but failed. Any idea?
    Thx
    Lopi

  10. [...] Ein viel geliebter Klassiker ist die Erstellung von GANTT-Diagrammen mit Excel. Wir hatten das Thema wiederholt schon hier. Chandoo.org hat sich mal wieder mit einer neuen Variante hervorgetan: Das GANTT-Box-Chart. [...]

  11. David says:

    Hi Chandoo - fantastic xls. One thing I can't figure out how to do is adjust the alignment of the vertical axis. I would like to left align so that I could indent to represent sub tasks. Can that be done? Or is there a better way?

  12. Paul says:

    I've been trying to work out if there's a way to show weekends on the graph. The closest thing I've got is to add them on a secondary axis, but then I haven't been able to keep both axis lined up together! Any ideas?

    Following on from this - is it possible to show things like holidays?

Leave a Reply