While working in Excel, we often need to find a specific value from a dataset. If the dataset is quite large, then it becomes a pretty challenging task to find a value from a huge dataset. So, to solve this problem, we can use the** Lookup function** of Excel **VBA**. In this article, we will demonstrate **six **simple, and practical examples to **use the VBA Lookup function in Excel**. So, letâ€™s start this article and explore these examples.

**Excel VBA Lookup Function (Quick View)**

The overview of the **VBA Lookup **function is demonstrated in the following image. We will discover more about using the **VBA Lookup **function in different examples in later portions of this article.

**Table of Contents**hide

## Download Practice Workbook

The first workbook contains the overall uses of the **VBA** **Lookup** function whereas the second one is our sample dataset.

## Introduction to VBA Lookup Function in Excel

Now, letâ€™s familiarize ourselves with **Syntax**, **Argument**, and **Return Value **subsections of the **VBA Lookup **function to understand the basics of the function.

**Summary:**

- The
**VBA Lookup**function finds a value from a one-row or a one-column range.

**Syntax:**

The syntax of the **VBA Lookup **function is:

`WorksheetFunction.Lookup(Arg1,Arg2,[Arg3])`

**Arguments:**

Argument | Required/Optional | Explanation |
---|---|---|

Arg1 |
Required | The first argument is the lookup value. It can be any text, number, logical values, etc. It can also be a cell reference that refers to a value. |

Arg2 |
Required | This is the range where we will search for our lookup value. This range is also known as the lookup vector. |

[Arg3] |
Optional | This is the range that contains our desired output. It is also known as the result vector. |

**Return Value:**

The **VBA Lookup **function returns the value from the **result vector **after comparing the **lookup value **with the **lookup vector**.

**Remarks:**

- For the
**VBA Lookup**function to work properly, the**lookup vector**column should be sorted in**ascending order**first. - The size of the
**result vector**should be the same as the**lookup vector**.

**Read More: Advanced Excel Lookup Functions (9 Examples)**

## 6 Suitable Examples of Using VBA Lookup Function in Excel

In this section of the article, we will discuss **six **practical examples of using the **VBA Lookup** function in Excel. Letâ€™s say, we have the **Laptop Prices in Micro Center Store **as our dataset. Using this dataset, we will demonstrate the application of the **VBA Lookup **function in the following sections.

Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.

### 1. Using Cell Reference as Lookup Value

In the first example, we will use the **Cell Reference** as a **lookup value **in the **VBA Lookup **function in Excel. Now letâ€™s follow the steps mentioned below to this.

__Step 01: Sort Lookup Vector in Ascending Order__

First, you need to **Sort** the **lookup vector **in **ascending order**. Here, we will use the name of the **Product **as our **lookup value**. Therefore, our **lookup vector **will be in the range **C5:C14**.

- Now, select the cells of the range
**C5:C14**as shown in the following image. - After that, go to the
**Home**tab from**Ribbon**. - Then, click on the
**Sort & Filter**option from the**Editing**group. - Subsequently, choose the
**Sort A to Z**option from the drop-down.

As a result, the **Product **column will be sorted in **ascending order**.

__Step 02: Insert New Module in VBA Editor__

- Firstly, go to the
**Developer**tab from**Ribbon**. - After that, choose the
**Visual Basic**option from the**Code**group.

As a result, the **Microsoft Visual Basic for Applications **window will appear on your worksheet.

- Now, go to the
**Insert**tab in the**Microsoft Visual Basic for Applications**window. - Then, choose the
**Module**option from the drop-down.

__Step 03: Write and Save VBA Code in New Module__

- Firstly, write the following code in the newly created
**Module**.

```
Sub lookup_with_cell_reference()
Range("C17").Value = WorksheetFunction.Lookup(Range("B17").Value, _
Range("C5:C14"), Range("F5:F14"))
End Sub
```

**Code Breakdown**

- Firstly, we created a sub-procedure named
**lookup_with_cell_reference**. - After that, we used
**WorksheetFunction.Lookup Method**to find the**Price**for the**lookup value**in cell**B17**and assigned the value to cell**C17**. - Then, we ended the sub-procedure.

- After writing the code, click on the
**Save**icon.

__Step 04: Run Macro to Use VBA Lookup Function__

- Firstly, use the keyboard shortcut
**ALT + F11**to return to the worksheet. - Now, enter the name of the product in cell
**B17**. This is our**lookup value**. - Afterward, use the keyboard shortcut
**ALT + F8**to open the**Macro**dialogue box. - Now, in the
**Macro**dialogue box, choose the**lookup_with_cell_reference**option. - Finally, click on
**Run**.

Consequently, the **Price **of the **MacBook Pro **will appear in cell **C18**, as demonstrated in the image below.

**Read More: How to Lookup Multiple Values in Excel (10 Ways)**

### 2. Utilizing User Input as Lookup Value

In the second example, we will use the **user input **as our lookup value to find the **Price **of a laptop using the **VBA Lookup **function in Excel. We will take the lookup value from the user by utilizing the **InputBox function** of Excel **VBA**. Now letâ€™s follow the steps mentioned below to do this.

__Steps:__

- Firstly,
**follow the steps mentioned in Step 01 and Step 02 of the first method**. - After that, write the following code in the newly created
**Module**.

```
Sub user_defined_lookup()
Product = InputBox("Enter the Name of the Product: ")
Range("B17").Value = WorksheetFunction.Lookup(Product, _
Range("C5:C14"), Range("F5:F14"))
End Sub
```

**Code Breakdown**

- Firstly, we created a sub-procedure named
**user_defined_lookup**. - Then, introduced a variable named
**Product**. - After that, we used the
**InputBox**function of Excel to take input from the user and then we assigned the user input into the variable named**Product**. - Following that, we used
**WorksheetFunction.Lookup Method**to find the**Price**for the**lookup value**defined by the user and assigned the value to cell**B17**. - Finally, we ended the sub-procedure.

- Then, click on the
**Save**option.

- After that, use the keyboard shortcut
**ALT + F11**to return to the worksheet. - Then, enter the name of the product in cell
**B17**. This is our**lookup value**. - Subsequently, use the keyboard shortcut
**ALT + F8**to open the**Macro**dialogue box. - Following that, in the
**Macro**dialogue box, choose the**lookup_with_cell_reference**option. - Lastly, click on
**Run**.

- Consequently, a dialogue box will appear on your screen. In the dialogue box, enter the name of the
**Product**. - Then, click
**OK**.

Subsequently, in cell **B17**, you will have the **Price **of the **Product **that you have entered in the dialogue box.

**Read More: How to Use LOOKUP Function Among Multiple Sheets in Excel**

### 3. Employing a Range of Lookup Values

Now, we will use a** range of lookup values** instead of a single lookup value to find the **Prices **of selected laptops using the **VBA Lookup **function in Excel. To do this, letâ€™s use the instructions outlined below.

__Steps:__

- Firstly,
**apply the steps mentioned in Step 01 and Step 02 of the first method**. - After that, write the following code in the newly created
**Module**.

```
Sub range_of_lookup_values()
Range("C17:C19").Value = WorksheetFunction.Lookup _
(Range("B17:B19").Value, Range("C5:C14"), Range("F5:F14"))
End Sub
```

**Code Breakdown**

- Firstly, we created a sub-procedure named
**range_of_lookup_values**. - Following that, we used
**WorksheetFunction.Lookup Method**to find the**Price**for the**lookup value**in range**B17:B19**and assigned the value to range**C17:C19**. - Lastly, we ended the sub-procedure.

- After writing the code, click on the
**Save**icon.

- Following that, use the keyboard shortcut
**ALT + F11**to return to the worksheet. - Now, enter the names of the products in range
**B17:B19**. This is our**range of lookup values**. - After that, use the keyboard shortcut
**ALT + F8**to open the**Macro**dialogue box. - Afterward, in the
**Macro**dialogue box, choose the**range_of_lookup_values**option. - Finally, click on
**Run**.

Consequently, you will have the prices for the selected **lookup values **as demonstrated in the following picture.

### 4. Calculating Grades Based on Obtained Marks

In the fourth example, we are going to calculate the **Grades **based on **Obtained Marks **by using the **VBA Lookup **function in Excel. Letâ€™s say, we have the **Grading System of XYZ School **as our dataset. Using this dataset, we will calculate the **Grades**. So, letâ€™s follow the procedure discussed in the following section.

__Steps:__

- Firstly,
**follow the steps mentioned in Step 01 and Step 02 of the first method**. - Then, write the following code in the newly created
**Module**.

```
Sub calculating_grade()
Range("C14").Value = WorksheetFunction.Lookup( _
Range("B14").Value, Range("B5:B11"), Range("C5:C11"))
End Sub
```

**Code Breakdown**

- Firstly, we created a sub-procedure named
**calculating_grade**. - Following that, we used
**WorksheetFunction.Lookup Method**to find the**Grade**for the**Obtained Marks**in cell**B14**and assigned the value to range**C14**. - Finally, we ended the sub-procedure.

- Subsequently, click on the
**Save**icon.

- After that, use the keyboard shortcut
**ALT + F11**to return to the worksheet. - Subsequently, insert the
**Obtained Marks**in cell**B14**. - After that, use the keyboard shortcut
**ALT + F8**to open the**Macro**dialogue box. - Afterward, in the
**Macro**dialogue box, choose the**calculating_grade**option. - Finally, click on
**Run**.

As a result, you will have the **Achieved Grade **based on **Obtained Marks **in cell **C14**.

### 5. Extracting Value from Another Worksheet

In this section of the article, we will extract our desired value from a **different worksheet**. Here, we have intentionally removed the **Price **column from the dataset. We have used the **Price **column as the **result vector** in the previous methods. In this method, we will use the **Price **column from a different worksheet and use it as the **result vector**. Here, we will use the **Price **column from the worksheet named **Cell Reference**. Letâ€™s use the steps mentioned in the following section.

__Steps:__

- Firstly,
**use the steps mentioned in Step 01 and Step 02 of the first method**. - Subsequently, write the code given below in the newly created
**Module**.

```
Sub lookup_from_different_worksheet()
Range("C17").Value = WorksheetFunction.Lookup(Range("B17") _
.Value, Range("C5:C14"), Sheets("Cell Reference").Range("F5:F14"))
End Sub
```

**Code Breakdown**

- Firstly, we created a sub procedure named
**lookup_from_different_worksheet**. - Then, we used
**WorksheetFunction.Lookup Method**to find the**Price**for the**lookup value**in cell**B17**and assigned the value to cell**C17**. - Here, we defined the
**result vector**from**another worksheet**named**Cell Reference**. - Lastly, we ended the sub procedure.

- After writing the code, click on the
**Save**option.

- Following that, use the keyboard shortcut
**ALT + F11**to return to the worksheet. - After that, enter the name of the product in cell
**B17**. This is our**lookup value**. - After that, use the keyboard shortcut
**ALT + F8**to open the**Macro**dialogue box. - Afterward, in the
**Macro**dialogue box, choose the**lookup_from_different_worksheet**option. - Finally, click on
**Run**.

Consequently, you will have the following output in cell **C17**, as shown in the image below.

**Read More: How to Lookup Value from Another Sheet in Excel (3 Easy Methods)**

### 6. Finding Value from Another Workbook

In our final example, we will demonstrate how we can find and extract value from a **different workbook** using the **VBA Lookup **function in Excel. Here, we have created a new workbook named **Laptop Prices **and changed the values of the **Price **column so that we can differentiate our results from the previous workbook. We have named this worksheet as **Dataset**. Now letâ€™s follow the steps outlined below.

__Steps:__

- Firstly,
**use the steps outlined in Step 01 and Step 02 of the first method**. - Following that, write the following code in the newly created
**Module**.

```
Sub lookup_from_different_workbook()
Range("C17").Value = WorksheetFunction.Lookup(Range("B17").Value _
, Range("C5:C14"), Workbooks("Laptop Prices.xlsx").Sheets _
("Dataset").Range("F5:F14"))
End Sub
```

**Code Breakdown**

- Firstly, we created a sub procedure named
**lookup_from_different_workbook**. - Then, we used
**WorksheetFunction.Lookup Method**to find the**Price**for the**lookup value**in cell**B17**and assigned the value to cell**C17**. - Here, we defined the
**result vector**from**another workbook**named**Laptop Prices**. Then, we also defined the**worksheet name**and the**range**of the**result vector**. - Lastly, we ended the sub procedure.

- Now, click on the
**Save**option.

- Subsequently, use the keyboard shortcut
**ALT + F11**to return to the worksheet. - Then, enter the name of the product in cell
**B17**. This is our**lookup value**. - Now, use the keyboard shortcut
**ALT + F8**to open the**Macro**dialogue box. - Following that, in the
**Macro**dialogue box, choose the**lookup_from_different_worksheet**option. - Lastly, click on
**Run**.

As a result, you will have the following output in cell **C17**. If you look carefully, you will see that the price of **Vivobook E200HA** was** â‚¬192** in the first workbook. But in cell **C17**, we haveÂ **â‚¬210**, which has come from the new workbook that we have created.

## Practice Section

In the **Excel Workbook**, we have provided a **Practice Section **on the right side of the worksheet. Please practice it yourself.

## Conclusion

So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to** use the VBA Lookup function in Excel**. If you have any questions, suggestions, or feedback related to this article, you can comment below. You can also have a look at our other useful articles on Excel functions and formulas on our website, **ExcelDemy**, a one-stop Excel solution provider.