Posts Tagged ‘microsoft excel’

Microsoft Excel VLOOKUP Can Save You Many Hours Of Work (Excel 2007, Excel 2003, Excel 2002, Excel 2000)

Sunday, May 10th, 2009

There’s a very powerful function in Microsoft Excel that many Excel users don’t know about.

Whether you are using Excel 2007, Excel 2003, Excel 2002 or Excel 2000, it could be saving you hours and hours of work.

It can reduce many hours of manual work down to just a few seconds once you’ve got it set up. I recently showed it to a new co-worker of mine and it reduced two days work down to a few seconds work.

Imagine what that would be worth to you.

How much time would that save you in the run of a year?

What would that be worth to your company?

VLOOKUP is a function that is used in a worksheet to return a value from a table (either in the same sheet, another sheet or another workbook) that is related to the value you give it.

Say you have a parts list in a table on one sheet containing thousands of parts numbers and their related information. The table shows the part number in the first column, the part name in the second column, the part price in the third column. On a separate sheet you have an invoice with columns for the Quantity, the Part #, the part Description, the Price and the Total.

Excel VLOOKUP example

Reduce hours of work down to seconds when you use Excel's VLOOKUP function


The Description and Price columns in the Invoice sheet contain VLOOKUP functions. When you enter the part number in the Part # column on the Invoice, the VLOOKUP function in the Description column looks down the first column of the parts table until it finds a match for the part number you entered, it then looks across the row for the related Description and returns it to the Invoice sheet. A similar function finds the price for the part number.

Here’s the syntax of a VLOOKUP Function:

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

In this sample the formula is =VLOOKUP($D19,parts_list!$A$1:$C$22,2,0)

The lookup_value is the value to search in the first column of the table.

The table_array can be two or more columns of data. You can use a reference to a range or a range name. The values in the first column of the table are the values searched by lookup_value. These values can be text, numbers, or logical values.

The col_index_num is the column number in table from which the matching value must be returned. A col_index_num of 1 returns the value from the first column in table; a col_index_num of 2 returns the value from the second column in table, and so on.

Range_lookup specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE (or 1), an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. The values in the first column of table must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. If FALSE (or 0), VLOOKUP will find only an exact match. In this case, the values in the first column of table do not need to be sorted. If there are two or more values in the first column of table that match the

lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

To avoid #N/A errors when a VLOOKUP function does not find a match, modify the formula with an IF statement

=IF(ISNA(formula),””,formula)

=IF(ISNA(VLOOKUP($D19,parts_list!$A$1:$C$22,2,0)),”",VLOOKUP($D19,parts_list!$A$1:$C$22,2,0))

I hope this helps you understand the power of the VLOOKUP function.

Welcome to The Excel Addict Blog

Wednesday, January 14th, 2009
Francis Hayes (The Excel Addict)

Francis Hayes (The Excel Addict)

Hi, my name is Francis Hayes. I have been using Microsoft Excel for more than 18 years and have become ‘addicted’ to the almost unlimited potential it gives to anyone who does a little digging for its hidden gems.

Most Excel users just use it as a plain spreadsheet. I’m much more curious and adventurous. For the past 18 years, I have been uncovering Excel’s hidden secrets that open up a new world of possibilities that 95%+ of Excel users don’t know about.

Over the years, I became frustrated because of the attitude of most Excel users who continue using Excel as a ‘glorified calculator’ even after been shown some of it’s time-saving, money-saving, frustration-decreasing, accuracy-assuring features.

In 2003, to release some of my frustration, I created my own website, TheExcelAddict.com, and started publishing a FREE weekly newsletter, Spreadsheet Tips From An Excel Addict to share my knowledge with an audience of Microsoft Excel users who ARE interested in expanding their Excel knowledge.

After more than 7 years and 18,000+ subscribers, I decided to create this blog to further spread my message to those who may be looking for better and faster ways to do things in Excel and want to differentiate themselves from the other 95% of Excel users.

It may be hard to believe, but just the fact that you’re reading this blog tells me that you are in the top 5% of Excel users.

My passion is helping others discover the amazing power hidden in Excel. If you’re as passionate about Excel as I am, I hope you will come back to my blog regularly to learn some of the things I have discovered over 18 years of using Microsoft Excel and want to share with the world.

P.S. If you love Excel as much as I do, get my weekly newsletter and see how learning a few of these tips every week will, over time, make your productivity soar beyond what you’ve ever thought possible.

Sign up NOW for free at http://www.TheExcelAddict.com