How do I perform a VLOOKUP and MATCH in Excel 2010

Posted on 2012-08-28
Last Modified: 2012-08-30
I recently pulled a list of accounts and current balances.

In the Jan tab there is a column called SPENT this is where I would like to populate the value.
The source data is a tab called ACTUAL.

I would liek to match up weach field by the  account type using MATCH or somehting of the sort.

Could anyone help me with this and teach me?

Thank you

Question by:manelson05
    LVL 2

    Expert Comment

    Based on your description I have put together a simple workbook.  I may not be exactly correct but I am going to describe my solution.  On the First tab in the workbook I have the January Budget figures and a column called Spent, which will lookup the acutal numbers from the data in the Actual worksheet.

    This example relies on a few things.  First of all that there is an exact 1:1 match between the colum names. In other words when you go to look up the word "Rent" from the Jan Sheet ini the Actual Sheet there can be only a single entry in the Actual sheet for Rent.

    In actuality, i would use Range Names to specify the range in the Actual sheet but to keep it simple, I have just specified an absolute range manually.

    Notice that in the VLookup formula, I have specified that it be an exact match.

    That is basically it.  If I assumed something incorrectly, feel free to clarify.

    Good Luck

    LVL 13

    Expert Comment

    In your spent column,
    =VLOOKUP(B5,'Budget Plan'!B:E,4,FALSE)

    Author Comment


    I am needing to pull by account number in JAN to from ACTUAL tab using Account number.

    Thank you

    LVL 12

    Accepted Solution

    You're vlookup formula for D5 will be VLOOKUP(A5,ACTUAL!A$2:B$400,1,0)

    But you won't find any matches until you remove the white spaces from the Account numbers in the Actual tab (try the trim function).  

    Your Actual account numbers are 129 characters long including white spaces,
    the Budget Plan account numbers are 15 characters.

    Author Comment

    When I enter the following  =VLOOKUP(A5,ACTUAL!A$2:B$400,1,0)

    I end up with

    01-06-610010-00      Direct Sales Travel-Lodging       $7,500.00       01-06-610010-00      #VALUE!
    IT si pulling account number from ACTUAL not dollar amount.

    Can you help me?
    LVL 13

    Assisted Solution

    that 1 at the end says you're putting the value from the 1st column in the range, which is the same as the lookup value.

    It needs to be a 2. If it needs to be more than 2, then your range needs expanded, as it currently only has 2 columns.

    Author Comment


    DO you have a good reference for learning Excel on ahigher level, Is Chandoo pretty good site?
    How did you learn?

    Author Closing Comment

    Very helpful and learnign the basics of this incredible tool! I love vlookup!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    I found an issue or “bug” in the SonicOS platform (the firmware controlling SonicWALL security appliances) that has to do with renaming Default Service Objects, which then causes a portion of the system to become uncontrollable and unstable. BACK…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now