answersLogoWhite

0


Best Answer

Here's an example scenario.

Let's say in range A1:C200 my data includes the following:

Column A: Employee Name

Column B: Date they worked

Column C: Total hours they worked that day.

I have 10 employees, each of whom worked 20 days out of the month.

They are divided into four teams.

In Column D I want to know what team they are on.

If I have a table listing each employee's name and team, I could use the VLOOKUP to populate column D for me.

I enter or copy and paste the Employee Name/Team list in columns H:I, Employee name in H, Team in I. Ten rows for ten employees (H1:I10).

In cell D1 I enter the following formula:

=VLOOKUP(A1,H:I,2,FALSE)

A1 points to one specific cell in my data table, the cell I want to link to another table.

H:I tells Excel where to look for the same thing it found in A1.

2 tells it that once it finds a match in H:I, count over 2 columns and return that value.

FALSE tells it that I want only an exact match (if you're using numbers or time, you could use TRUE or blank here, VLOOKUP will then look for the nearest value, but obviously it doesn't make any sense to "round" a name.)

As I carry that formula down in column D, A1 increments to A2, A3, A4, etc. The remainder of the variables in the formula remain the same.

Column D will now automatically populate with the team name, as shown in range H:I

If a value exists in column A but not in column H, it will return #N/A.

If a value exists in column A, and in column H, but column I is blank, it will return "0".

Caveats:

* It takes time to process the VLOOKUP. My example of 200 line items should take seconds, but if you have a very large data table, or if your lookup reference is very large, it will take longer. Watch the status bar, it will give you a percentage complete. Don't do anything else in Excel while it's processing, otherwise it will stop calculating.

* VLOOKUP, when using "FALSE", looks for an exact match. "John Smith" is not the same as "John Smith" or "John Smith " (note extra spaces).

* When referring to the table array (H:I in the above example), the information you're trying to match (Employee Name) has to be in the first column of the referenced range. It does not have to be the first column in the range itself (for example, column G could be Supervisor Name), just the first column referenced in the formula.

I hope this helps, I use VLOOKUP pretty much daily and it's incredibly useful.

User Avatar

Wiki User

15y ago
This answer is:
User Avatar

Add your answer:

Earn +20 pts
Q: How do you use V Look up function in Microsoft Excel?
Write your answer...
Submit
Still have questions?
magnify glass
imp
Related questions

How do you do standard deviation on Microsoft Excel?

Use the STDEV() function.


How do you use the all function in Excel?

you can get help in the Microsoft excel itself by pressing F1 key.


What is the step function of Microsoft Excel?

There is no step function in Excel. However, you can use excel to create a Step Function Chart. See related links for a video to explain the process.


Do radiological technician use Microsoft Excel?

Yes, a radiological technician could use a spreadsheet such as Microsoft Excel in their work.Yes, a radiological technician could use a spreadsheet such as Microsoft Excel in their work.Yes, a radiological technician could use a spreadsheet such as Microsoft Excel in their work.Yes, a radiological technician could use a spreadsheet such as Microsoft Excel in their work.Yes, a radiological technician could use a spreadsheet such as Microsoft Excel in their work.Yes, a radiological technician could use a spreadsheet such as Microsoft Excel in their work.Yes, a radiological technician could use a spreadsheet such as Microsoft Excel in their work.Yes, a radiological technician could use a spreadsheet such as Microsoft Excel in their work.Yes, a radiological technician could use a spreadsheet such as Microsoft Excel in their work.Yes, a radiological technician could use a spreadsheet such as Microsoft Excel in their work.Yes, a radiological technician could use a spreadsheet such as Microsoft Excel in their work.


Looking for advice on Excel's amortization calculator?

Yes it is easy to use it you are familiar with Microsoft Excel. If you are having trouble you can always use the help function within the program.


What is v - look up?

VLOOKUP is a function of Microsoft Excel. You can use VLOOKUP to find a value in a table or list on an Excel sheet. It looks at a value in a column and finds the corresponding value in another column in the same row.


Why is Excel not itself database but can be used as database?

Microsoft Excel is not a database, it is a spreadsheet. You could use it for a rudimentary database application, but that is not it's primary function.


In Microsoft Excel functions can be entered using what?

You can enter them by just typing them in with the keyboard. You can also use the Insert Function facility.


How do you use Excel for typing Microsoft Word?

Excel and Microsoft word are two different programs. To ask how to you use excel for typing ms word does not make sense


How can you use Excel at home and at work?

go on to Microsoft excel and do what you want


Why do pharmacy technicians use Microsoft Excel?

Pharmacy technicians use Microsoft Excel because it is a fast and efficient means of organizing data.


What do you mean by Microsoft Excel?

Microsoft is a brand like Addidas or Prada. Excel is a programme that you use for producing spreadsheets.