[Voiceover] In this worksheet called 'approximate vlookup', we're going to be using the vlookup function, in cell I2, and then down the column, to look up information in this table right here. Working with “~” in excel. Ask Question 8. Some of your past answers have not been well-received, and you're in danger of being blocked from answering. Please pay close attention to the following guidance. Trouble getting VLOOKUP to work. Excel: VLOOKUP and INDEX+MATCH return #REF.

Hi Rajeev, I understand your problem as I myself have gone through a lot with my users. This works fine with locally installed Excel but when using Citrix there is a problem not only with VLookup but with many other Excel Features which you perform between 2 workbooks. All you can do is this: Open Excel Through Citrix (Blank Sheet).

Go to File-> Open and open 1st Workbook (WB1). Now Minimise this WB1 but make sure to minimise only WB1 and NOT the entire Excel Window. Now inside same Excel with WB1 minimised, open second workbook (WB2). Both your workbooks will be open now inside same excel window and WB1 will be minimised to the lower left side of the screen. Now all you Excel formulae will work fine. Although this resolution is a bit confusing initially for the users, but at lease it works. Regards, Asrar.

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. This means there will be multiple entries for the same person.