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.
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
I hope this helps you understand the power of the VLOOKUP function.