3 Comments

  • Vlookups consumed my life the last few months of my most recent CQI/Eval position. Very helpful and quite the time saver once you get the hang of it though. Thanks for sharing these tips and congrats on 100,000 views!

  • Col Delane says:

    Ann, I think you’re rather underselling the VLOOKUP function by saying that it is for merging and combining datasets. Sure you can use the function for such tasks, and these don’t need to be as extensive as your example (e.g. you may just wish to add a single value which is returned from another table/database) but it can do more than that – like grading raw numeric scores into brackets/bins, returning the appropriate income tax or commission rate for a given level of income or sales, etc.
    VLOOKUP is a great function, though – like most other Excel functions – it does have its limitations and traps for new players.

  • Vlookup is the star function, but I prefer Index & Match. It can do all that Vlookup does, plus a lot more

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    How to Use Vlookup in Microsoft Excel to Combine Tables of Data

    Updated on: Jul 9th, 2014
    Data Analysis
    , , ,
    Example of how vlookup allows you to combine different spreadsheets, tabs, or data tables into a single master table that you can use for your  analysis.

    I’m celebrating 100,000 YouTube views with an article on my favorite spreadsheet function: vlookup!

    Vlookup is for merging and combining datasets, like when you’ve got program participants’ demographic data in one place and their scores or outcomes in another place. 

    I can’t tell you how many times I’ve watched researchers and evaluators waste hours of their time by trying to merge these spreadsheets manually (e.g., by copying and pasting back and forth).

    You’ve got better things to do with your time, don’t you?

    Vlookup to the rescue!

    Vlookup is for Combining Datasets

    In the example below, I had demographic data for 430 people in one spreadsheet and their mental health counselors’ rating of their progress for 137 people in a second spreadsheet.

    I knew I was supposed to have data for 460 people total.

    I couldn’t simply copy and paste columns of data together because, in real-life analyses, each spreadsheet contains a different number of people. (Don’t you just love missing data?) 

    This magical formula allows you to combine those different spreadsheets, tabs, or data tables into a single contiguous dataset that you can use for your tabulations.
    Example of how vlookup allows you to combine different spreadsheets, tabs, or data tables into a single master table that you can use for your  analysis.

    Vlookup Isn’t Easy

    A disclaimer: vlookup has a learning curve. 

    I first learned this function from Agata Jose-Ivanina when we were working together back in 2008. Bless her heart, she explained and re-explained the function to me a million times. I practiced on my own, and finally–probably a full month later–I got it.

    So don’t worry if this function seems tricky at first. Eventually everything will click in your brain, and you’ll be on your way to saving hours and hours of your time.

    Vlookup Needs 4 Things

    The function contains four pieces of information.

    It looks like this: =vlookup(lookup_value,table_array,col_index_num,range_lookup).

    And here’s what each piece of the function really means:

    • lookup_value: The cell that contains the person’s ID number. The ID numbers are the link or key that connect all the spreadsheets together. The ID numbers must be located in the first column of each table – in the first column of your new combined dataset and in the first column of every single table from which you’re pulling data.
    • table_array: This is the table from some other spreadsheet or other file from which you’re pulling data.
    • col_index_num: This is the column in that table where you’re pulling the data from. Just type in the number of the column. For example, if you want to pull in data from Column C of another table, you’d type “3.”
    • range_lookup: Always type “false” and you’ll be in good shape.

    When to Use Vlookup

    Check out these three step-by-step tutorials to learn more.

    I’ve purposefully repeated content through the tutorial series so that you can hear the information multiple times in multiple ways.

    Why to Use Vlookup

    How to use =vlookup():

    Advanced Vlookup Time-Savers

    Comfortable with vlookup?

    Apply time saver #1 and time saver #2 to take your spreadsheet to the next level.

    Hlookup

    Then, try your hand at hlookup.

    Hlookup is essentially the same as vlookup, but the data table is rotated on its axis:

    • vlookup is for vertical datasets (the ID numbers go up and down in a column) and
    • hookup is for horizontal datasets (the ID numbers go left to right in a row).

    Bonus! Download the Materials

    Want to practice these skills? You can purchase the spreadsheets used in the videos.

    Purchase the Spreadsheets From the Tutorial Videos

    More about Ann K. Emery
    Ann K. Emery is a sought-after speaker who is determined to get your data out of spreadsheets and into stakeholders’ hands. Each year, she leads more than 100 workshops, webinars, and keynotes for thousands of people around the globe. Her design consultancy also overhauls graphs, publications, and slideshows with the goal of making technical information easier to understand for non-technical audiences.

    3 Comments

  • Vlookups consumed my life the last few months of my most recent CQI/Eval position. Very helpful and quite the time saver once you get the hang of it though. Thanks for sharing these tips and congrats on 100,000 views!

  • Col Delane says:

    Ann, I think you’re rather underselling the VLOOKUP function by saying that it is for merging and combining datasets. Sure you can use the function for such tasks, and these don’t need to be as extensive as your example (e.g. you may just wish to add a single value which is returned from another table/database) but it can do more than that – like grading raw numeric scores into brackets/bins, returning the appropriate income tax or commission rate for a given level of income or sales, etc.
    VLOOKUP is a great function, though – like most other Excel functions – it does have its limitations and traps for new players.

  • Vlookup is the star function, but I prefer Index & Match. It can do all that Vlookup does, plus a lot more

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    You Might Like

    Our complimentary mini course for beginners to dataviz. Takes 45 minutes to complete.

    Enroll

    How to Bring Your Technical Tables to Life

    Just because I’m pro-graph, I’m not anti-table. Technical tables have so much value, especially as visual appendices for reports. In this blog post, you’ll get ideas for bringing your technical tables to life.

    More »

    Need some time-savers in Excel? Feel like all the formulas are taking forever? Learn how to clean, analyze, and tabulate your dataset.

    Enroll

    Subscribe

    Not another fluffy newsletter. Get actionable tips, videos and strategies from Ann in your inbox.