Micorsoft Excel Formulas : Vlookup function

Since Microsoft Excel is the most common formats of office reports and analysis, this blog can serve as a reference for anyone to be more efficient in using excel.

There will be tips on how to use excel in the office, using basic to advance formulas & functions, links to templates and other related information that can be helpful in your Microsoft Excel projects.

Let's get the ball rolling...for this week let me share with you how to use the function "vlookup".

VLOOKUP

What Does It Do ?

This function scans down the row headings at the side of a table to find a specified item.
When the item is found, it then scans across to pick a cell entry.

Syntax (how this is enter as a formula)

=VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted)
The ItemToFind is a single item specified by the user.
The RangeToLookIn is the range of data with the row headings at the left hand side.
The ColumnToPickFrom is how far across the table the function should look to pick from.
The Sorted/Unsorted is whether the column headings are sorted. TRUE for yes, FALSE for no.

Let's put VLOOKUP to work...

Let’s take a look at the VLOOKUP function. In our example we have a company with a list of part numbers, along with information about them. Let’s asssume this list is very long, and they would like to enter a part# in a cell and have it return information about that part quickly.

This picture shows our desired result. In the orange cell we enter a part number, then the location and price will automagically display

So how do we accomplish this? Here is the VLOOKUP in layman’s syntax

=VLOOKUP(CellToLookup ,RangeToLookIn, WhichColumnToReturn, ExactMatch?)

And now in practice:

1. We need a list of data sorted by the first column.
2. In a blank cell that we would like to return a result from the list, type =VLOOKUP(
3. Click on the cell where we will enter the value to lookup (this will enter the cell in your formula)
4. Hit , (comma)
4. Click and drag to select the entire list including headers
6. Hit, (comma)
7. Type the numerical value of the column you would like to return (A=1, B=2, C=3,,,,)
8. Hit ,(comma)
9. Type “False” and hit enter

Enter “TRUE” to find an approximate match, one reason you might use approximation is on data with Typos or poor standards, for example “MR Man” and it’s actually “MR. Man”.

This works across multiple worksheets. If you would like to store your data in one sheet, and your VLOOKUP in another, it’s the same process.

You'll save a lot of time with this Microsoft Excel function.