Exact Match | Approximate Match | Vlookup Looks Right | First Match | Vlookup is Case-insensitive | Multiple Criteria | #N/A error | Multiple Lookup Tables | Index and Match | Xlookup

Entering data into worksheet cells is always a three-step process; these steps are as follows: Click on the cell where you want the data to go. Type the data into the cell. Press the Enter key on the keyboard or click on another cell with the mouse. To perform advanced lookups, you'll need INDEX and MATCH. Maybe this is one step too far for you at this stage, but it shows you one of the many other powerful formulas Excel has to offer. If you have Excel 365, use XLOOKUP instead of VLOOKUP. The XLOOKUP function is easier to use and has some additional advantages.

If you have a slicer on a PivotTable already, you can use that same slicer to filter another PivotTable. Please note that this only works when the two PivotTables use the same data source. First create a PivotTable that is based on the same data source as the PivotTable that already has the slicer you want to reuse. Adding rows and columns of numbers is one of the most common operations in Excel. To make this job easier, use the SUM function. Quickly Sum Columns or Rows of Numbers in Excel shows you how to: Understand the SUM function syntax and arguments. Open an existing Excel document. If you want to open an existing Excel document at any time, simply double-click the document in question. This will bring up the document in an Excel window. Skip this step if you want to open a new document in Excel.

The VLOOKUP function is one of the most popular functions in Excel. This page contains many easy to follow VLOOKUP examples.

Exact Match

Most of the time you are looking for an exact match when you use the VLOOKUP function in Excel. Let's take a look at the arguments of the VLOOKUP function.

1. The VLOOKUP function below looks up the value 53 (first argument) in the leftmost column of the red table (second argument).

2. The value 4 (third argument) tells the VLOOKUP function to return the value in the same row from the fourth column of the red table.

Note: the Boolean FALSE (fourth argument) tells the VLOOKUP function to return an exact match. If the VLOOKUP function cannot find the value 53 in the first column, it will return a #N/A error.

3. Here's another example. Instead of returning the salary, the VLOOKUP function below returns the last name (third argument is set to 3) of ID 79.

Approximate Match

Let's take a look at an example of the VLOOKUP function in approximate match mode (fourth argument set to TRUE).

1. The VLOOKUP function below looks up the value 85 (first argument) in the leftmost column of the red table (second argument). There's just one problem. There's no value 85 in the first column.

2. Fortunately, the Boolean TRUE (fourth argument) tells the VLOOKUP function to return an approximate match. If the VLOOKUP function cannot find the value 85 in the first column, it will return the largest value smaller than 85. In this example, this will be the value 80.

3. The value 2 (third argument) tells the VLOOKUP function to return the value in the same row from the second column of the red table.

Note: always sort the leftmost column of the red table in ascending order if you use the VLOOKUP function in approximate match mode (fourth argument set to TRUE).

Vlookup Looks Right

The VLOOKUP function always looks up a value in the leftmost column of a table and returns the corresponding value from a column to the right.

1. For example, the VLOOKUP function below looks up the first name and returns the last name.

2. If you change the column index number (third argument) to 3, the VLOOKUP function looks up the first name and returns the salary.

Note: in this example, the VLOOKUP function cannot lookup the first name and return the ID. The VLOOKUP function only looks to the right. No worries, you can use INDEX and MATCH in Excel to perform a left lookup.

First Match

If the leftmost column of the table contains duplicates, the VLOOKUP function matches the first instance. For example, take a look at the VLOOKUP function below.

Explanation: the VLOOKUP function returns the salary of Mia Clark, not Mia Reed.

Vlookup is Case-insensitive

The VLOOKUP function in Excel performs a case-insensitive lookup. For example, the VLOOKUP function below looks up MIA (cell G2) in the leftmost column of the table.

Explanation: the VLOOKUP function is case-insensitive so it looks up MIA or Mia or mia or miA, etc. As a result, the VLOOKUP function returns the salary of Mia Clark (first instance). Use INDEX, MATCH and EXACT in Excel to perform a case-sensitive lookup.

Multiple Criteria

Do you want to look up a value based on multiple criteria? Use INDEX and MATCH in Excel to perform a two-column lookup.

Note: the array formula above looks up the salary of James Clark, not James Smith, not James Anderson.

#N/A error

If the VLOOKUP function cannot find a match, it returns a #N/A error.

1. For example, the VLOOKUP function below cannot find the value 28 in the leftmost column.

2. If you like, you can use the IFNA function to replace the #N/A error with a friendly message.

Note: the IFNA function was introduced in Excel 2013. If you're using Excel 2010 or Excel 2007, simply replace IFNA with IFERROR. Remember, the IFERROR function catches other errors as well. For example, the #NAME? error if you accidentally misspell the word VLOOKUP.

Multiple Lookup Tables

When using the VLOOKUP function in Excel, you can have multiple lookup tables. You can use the IF function to check whether a condition is met, and return one lookup table if TRUE and another lookup table if FALSE.

1. Create two named ranges: Table1 and Table2.

2. Select cell E4 and enter the VLOOKUP function shown below.

Explanation: the bonus depends on the market (UK or USA) and the sales amount. The second argument of the VLOOKUP function does the trick. If UK, the VLOOKUP function uses Table1, if USA, the VLOOKUP function uses Table2. Set the fourth argument of the VLOOKUP function to TRUE to return an approximate match.

3. Press Enter.

4. Select cell E4, click on the lower right corner of cell E4 and drag it down to cell E10.

Note: for example, Walker receives a bonus of $1,500. Because we're using named ranges, we can easily copy this VLOOKUP function to the other cells without worrying about cell references.

Excel Spreadsheets For Dummies

Index and Match

Instead of using VLOOKUP, use INDEX and MATCH. To perform advanced lookups, you'll need INDEX and MATCH. Maybe this is one step too far for you at this stage, but it shows you one of the many other powerful formulas Excel has to offer.

Xlookup

If you have Excel 365, use XLOOKUP instead of VLOOKUP. The XLOOKUP function is easier to use and has some additional advantages.

-->

How Do You Use Excel On A Mac

You can use most Microsoft Excel worksheet functions in your Visual Basic statements. For a list of the worksheet functions you can use, see List of worksheet functions available to Visual Basic.

Note

Some worksheet functions are not useful in Visual Basic. For example, the Concatenate function is not needed because in Visual Basic you can use the & operator to join multiple text values.

Calling a worksheet function from Visual Basic

In Visual Basic, the Excel worksheet functions are available through the WorksheetFunction object.

The following Sub procedure uses the Min worksheet function to determine the smallest value in a range of cells. First, the variable myRange is declared as a Range object, and then it is set to range A1:C10 on Sheet1. Another variable, answer, is assigned the result of applying the Min function to myRange. Finally, the value of answer is displayed in a message box.

If you use a worksheet function that requires a range reference as an argument, you must specify a Range object. For example, you can use the Match worksheet function to search a range of cells. In a worksheet cell, you would enter a formula such as =MATCH(9,A1:A10,0). However, in a Visual Basic procedure, you would specify a Range object to get the same result.

How Do You Use Excel Online

Note

Visual Basic functions do not use the WorksheetFunction qualifier. A function may have the same name as a Microsoft Excel function and yet work differently. For example, Application.WorksheetFunction.Log and Log will return different values.

Inserting a worksheet function into a cell

To insert a worksheet function into a cell, you specify the function as the value of the Formula property of the corresponding Range object. In the following example, the RAND worksheet function (which generates a random number) is assigned to the Formula property of range A1:B3 on Sheet1 in the active workbook.

Example

This example uses the worksheet function Pmt to calculate a home mortgage loan payment. Notice that this example uses the InputBox method instead of the InputBox function so that the method can perform type checking. The Static statements cause Visual Basic to retain the values of the three variables; these are displayed as default values the next time you run the program.

See also

Support and feedback

How Do You Use Excel Macros

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.