Search Contextures Sites
Custom Search

 

 

 

Contextures
Excel news
by email

 

 

 

 

Learn how to create Excel dashboards.

 

 

Excel Video Tutorials -- Excel VLOOKUP Function

These videos show how to use a VLOOKUP formula to pull data from a specific column in a master table, based on a lookup value, such as a name or grade.

Change Percentages to Letter Grades in a VLOOKUP formula
Excel VLOOKUP From Another Workbook
Find Product Price in Excel with the VLOOKUP Function
Hide Errors in a VLOOKUP Formula With IF and ISNA
Use MATCH in a VLOOKUP formula

Change Percentages to Letter Grades in a VLOOKUP Formula

When you create a Vlookup formula, do you want an exact match? Is an approximate match okay? If you use TRUE as the last argument, or omit the last argument, an approximate match can be returned. In this video, TRUE is used as the last argument, so the letter grade that is closest to the number grade is returned.

A lookup table shows the minimum score for each letter grade, and on the report card, the VLookup function is used to change percentages to letter grades.

For written instructions for this Excel tutorial, see: VLookup Function

Excel VLOOKUP From Another Workbook

In a VLOOKUP formula, you can refer to a lookup table that is in a different Excel workbook.

In this example, the price list is in one workbook, and the VLOOKUP formula is in the order data entry workbook. It is easier to create the reference if both Excel workbooks are open when you create the formula. After you create the formula, the price list workbook can be open or closed, and the formula will continue to work correctly.

Find Product Price in Excel with the VLOOKUP Function

In an Excel worksheet, you can create a table that stores product pricing, or employee information, or any other data that you need to refer to frequently. Then, from other cells in the workbook, you can use a VLookup formula to look up the data from the master table.

In this video, you'll see how to use a VLookup function to find the price for the selected product. The VLookup function looks in the first column of the table, to find the selected product name. Then, it returns the product price, from the column number that was entered in the VLookup formula.

For written instructions for this Excel tutorial, see: VLookup Function

Hide Errors in a VLOOKUP Formula With IF and ISNA

When you create a Vlookup formula with an exact match, an #N/A error might be the result if the value is not found. Instead of showing the error, you can use the IF and ISNA functions to hide the error on the worksheet.

For written instructions for this Excel tutorial, see: VLookup Function

Use MATCH in a VLOOKUP Formula

VLookup formulas work vertically, looking for values down a column, then they return the value in a specific column of the row where that value is found. In this video, instead of typing a column number in the VLookup formula, the MATCH function and VLOOKUP function are used together, to create a flexible formula.

The Excel MATCH function can find a value in a list, and return its position. In this example, the MATCH function is used to find the column number, to use in the VLOOKUP function.

For written instructions for this Excel tutorial, see: Match Function

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

More Tutorials

Excel Tutorial Videos 01
Excel Tutorial Videos 02
Excel Tutorial Videos 03
Excel Tutorial Videos 04
Excel Tutorial Videos 05
Excel Tutorial Videos 06
Excel Tutorial Videos 07
Excel Tutorial Videos 08 
Excel Tutorial Videos 09 
Excel Tutorial Videos 10    
Excel Tutorial Videos 11   
Excel Tutorial Videos 12  

 

 

Privacy Policy

 

Contextures Inc., Copyright 2016
All rights reserved.

 

 

Last updated: June 11, 2015 9:12 AM