Computer Training & Support

We will open the world of knowledge for you!

Quick Tips

Quick Tip April Support/FAQ

VLookup Function in Excel 2010

You can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range.

 

The VLOOKUP function syntax:
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

 

The lookup_value argument is the value that you want to look up in the table, and table_array is the cell range or name of the table that contains both the value to look up and the related value to return. The col_index_num argument in the VLOOKUP function is the number of the column whose value you want to return.

 

The optional range_lookup argument in the VLOOKUP function is the logical TRUE or FALSE that specifies whether you want Excel to find an exact or approximate match for the lookup_value in the table_array. When you specify TRUE or omit the range_lookup argument, Excel finds an approximate match. When you specify FALSE as the range_lookup argument, Excel finds only exact matches.

 

EXAMPLE:
The figure below shows an example of using the VLOOKUP function to return either a 15% or 20% tip from a tip table. Cell F3 contains the VLOOKUP function:
=VLOOKUP(Pretax_Total,Tip_Table,IF(Tip_Percentage=0.15,2,3))
This formula returns the amount of the tip based on the tip percentage in cell F1 and the pretax amount of the check in cell F2.

VLookup Image

 

Excel then moves down the values in the first column of Tip_Table until it finds a match. If Excel finds that the value entered in the Pretax_Total cell ($16.50 in this example) doesn't exactly match one of the values in the first column of Tip_Table, the program continues to search down the comparison range until it encounters the first value that exceeds the pretax total (17.00 in cell A19 in this example).

Excel then moves back up to the previous row in the table and returns the value in the column that matches the col_index_num argument of the VLOOKUP function (this is because the optional range_lookup argument has been omitted from the function).

 

The col_index_num argument uses an IF statement to determine which column's value to return. In this case, if the Tip_Percentage value is 0.15, then the function returns the value in the second column of the table. Otherwise, it returns the value in the third column of the same row.

 

Need More Practice

Microsoft Free Online Practice

VLookup: What it is, and when to use it

News & Events

Now offering desk side support for all your training needs!

Most computer problems occur at a specific user's desktop. Solving these problems quickly is critical to keeping your employees happy and productive. We offer desktop support depending on your needs. Desk-Side Support is a fast and economical choice when you have a technical or semi-technical person in your office.

read                                



CompuSkillz

icon

Learn more about us

read