• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with VLOOKUP

J

jmh225

New Member
Hello-


Very novice user of Excel, so please bear with what may be an elementary how-to question. I have multiple worksheets in a single workbook containing employee names and "stats." Each month I get a new data set with new "stats" in a separate worksheet. I would like to incorporate this new data into an existing master worksheet to the extent the employee's name appears in the new data set.


For example, let's say I have "John Smith" in cell A2 of my master worksheet (Row 1 is my header row). Let's also say that I just received the new September stats that I would like to incorporate into the master worksheet. What I would like to do is to insert a formula that will grab the text containing the name "John Smith" in my master worksheet (in Cell A2), cross reference that name against all the names contained in the new September stats worksheet, and then perform a VLOOKUP to pull the data into the master worksheet. What I am trying to avoid is to have to type "John Smith" (and every other employee's name) into each formula, and instead just copy the formula throughout.


Hopefully that makes sense. If not I can elaborate further. Thank you so much in advance.


jason
 
my understanding is that there are 2 files, one being the master file and the other is the data file which refreshes periodically, right?


So in your master file I suppose the name John Smith is already in place. And that should only leave you to rename file name and/or file location (i.e. links) once you have all the vlookup formulae in place.


My guess from reading your question above is that your header row may change periodically and you don't want to recount the column index number? if that's the case, all you need to do is use index/match function on the part of the vlookup function.


Once that's done no matter how the header column changes on the data file it will automatically update itself. However, if there are new columns on the data sheet you want to add, you have to manually enter the title of the header onto your master file and adjust the table array accordingly.


Also, note that if a header column is removed from the data file from one period to another, the link may come back as #N/A. So save your prior period work in a different name so you can come back for it in the future.


Hope that helps.
 
Instead of doing this:

=VLOOKUP("John",'September'!A:B,2,FALSE)

use a cell reference like this:

=VLOOKUP($A2,'September'!A:B,2,FALSE)


that way when you copy it down in your master worksheet, it will use whichever name is there and you won't have to retype the formula.
 
or simply vlookup(A:A......)


Also make sure the names show up consistently. William as Bill, John as Jack, Frederick as Fred or Freddie, etc.


And maiden names too!
 
Fred & Luke-


Thanks so much for your feedback, this is great! One slight hiccup. I've plugged in the following formula (using the references Luke uses above): =VLOOKUP($A2,'September!A2:AD42,5,FALSE). That gets the right result for "John Smith," but then when I highlight the cell, and then do a CTL and drag to copy the formula into the cell below, I get a #REF! error message in the employee right below John Smith (let's call her Jane Doe).


When I click on the cell for Jane Doe, the formula looks like this: =VLOOKUP($A3,'September'!#REF!,5,FALSE). Obviously the hiccup is not copying over the A2:AD42 as the table_array. What is the best way to include the Table_array from the formula in the previous cell? I think if I can do this then we'll be golden. Thanks!


jason
 
Change your formula to have an absolute reference for the table:

=VLOOKUP($A2,'September!$A$2:$AD$42,5,FALSE)
 
You must log in or register to reply here.
Back
Top