• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 726
  • Last Modified:

How do I perform a VLOOKUP and MATCH in Excel 2010

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

MArk
jaylookup-01.xlsx
0
manelson05
Asked:
manelson05
2 Solutions
 
BobOxfordCommented:
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

Bob
Ex-Ex-Excel-example.xlsx
0
 
RyanProject Engineer, ElectricalCommented:
In your spent column,
=VLOOKUP(B5,'Budget Plan'!B:E,4,FALSE)
0
 
manelson05Author Commented:
I tried =VLOOKUP(A5,ACTUAL!A:B,4,FALSE)

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

Thank you

MArk
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Jared_SCommented:
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.
0
 
manelson05Author Commented:
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?
0
 
RyanProject Engineer, ElectricalCommented:
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.
0
 
manelson05Author Commented:
Awesome!

DO you have a good reference for learning Excel on ahigher level, Is Chandoo pretty good site?
How did you learn?
0
 
manelson05Author Commented:
Very helpful and learnign the basics of this incredible tool! I love vlookup!
0

Featured Post

What Security Threats Are We Predicting for 2018?

Cryptocurrency, IoT botnets, MFA, and more! Hackers are already planning their next big attacks for 2018. Learn what you might face, and how to defend against it with our 2018 security predictions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now