Be more productive—upgrade from Office 2007 to Office 365 today. Get more done by upgrading to Office 365. With an Office 365 subscription, you can install all the newest Office applications on multiple PCs, Macs, and other devices. You also get 1 TB of OneDrive cloud storage per user, 24/7 tech support, and more. Check to see if “Drag & Drop” is enabled in Excel. Select File > Options Under. Drag and drop' Click OK. Hopefully, that will allow your cursor to remain an arrow. Excel is a pretty amazing piece of software. On the surface it looks like a basic spreadsheet application, but just below this is a wealth of tools that can make even the most complex and time-consuming jobs easy. Take, for example, the VLOOKUP function. This amazing little tool can take seemingly meaningless data like model numbers or employee IDs and magically turn it into information we can read, such as names and email addresses. Instead of copying and pasting the correct email address next to every name in a list, VLOOKUP can automatically assign the right email address to the right person. It doesn’t matter if your list has 10 entries or 10,000 – VLOOKUP can make it work. Read on to see an example of VLOOKUP in action and find out how you can use it. All you need to use VLOOKUP is a set of data to reference and a list that needs to reference that data. If you want to see the example in action, just download the example file used below by clicking. Note: The example below shows you the manual method for creating a VLOOKUP and is shown with Office for Mac. This works the same for Windows, and while there are tools to build this inside of Excel, it’s far quicker to understand how it works and build these functions manually. RELATED: About VLOOKUP VLOOKUP is an Excel function that uses a referential table of data to apply necessary information to another place. This data can be another table in the workbook you’re using, another table on a worksheet, or in another Excel file altogether. This means if you use Employee numbers, for example, you can have an employee Excel file and reference data from it inside of countless other Excel files, as long as they’re in the same folder. So why is this useful? Let’s say you’re doing payroll. Most payroll applications only care about Employee ID or Social Security number, as each person only has one of these. But there can be multiple people with the same name. So, when you get your report it only has ID numbers on it. To make it readable you’d want to add names and possibly even departments. Instead of typing it by hand, you can reference it from a spreadsheet automatically. This is best shown with an example, so let’s take a look at using VLOOKUP for employees. How to Use VLOOKUP The first thing you need is your source data. This is the sheet that has both the hard-to-read information like an employee ID as well as the easy-to-read information like Employee Name. We’ll call this the Lookup Table. The VLOOKUP function uses four key pieces of information. This may seem imposing, but it’s actually pretty simple. For the sake of this tutorial we’ll do all of our work in one spreadsheet, but you can do this across Excel files in the same folder structure, too. Using the file shown above, we’re going to make a new page named “Working” and rename Sheet1 to “Lookup.” We’ll pretend we have timesheet data that needs names and departments attached to it. Microsoft word for mac os x 10.5.8 free download. This means there will be multiple entries for the same person. Here’s the data we’re starting with: The highlighted area is what we would like to fill in automatically with VLOOKUP. By referencing the data in the Lookup sheet, we’ll do this easily. The arguments VLOOKUP needs are: • lookup_value • table_array • col_index_num • range_lookup You can start the VLOOKUP function by typing =VLOOKUP in the cell you want data to show up in. For our example above, this would be cell D2. Excel will start to auto-format your formula, but just keep typing, as we’ll show you next what goes in here. Duplicate contacts killing your productivity?
0 Comments
Leave a Reply. |