How to Find Lookup a Value in an Array Using VBA in Excel (2 Methods)

Dataset Overview

To demonstrate the methods, we’ll use this Daily Sales-Fruit Section report of a certain grocery store. This dataset includes Product Name, Customer Name, and product Quantity in columns B, C, and D respectively.

Method 1 – Create a One-Dimensional Array

• Open Excel and go to the Developer tab.
• Click on Visual Basic in the Code group.

• In the Microsoft Visual Basic for Applications window, insert a new module.

• Paste the following code into the module:

Sub Lookup_Array()
Dim My_Array(20) As Integer
Dim x As Integer
Dim lookup_num As Variant
Dim msg As String
For x = 1 To 20
My_Array(x) = Int(Rnd * 20)
Debug.Print My_Array(x)
Next x
Input_Box:
lookup_num = InputBox("Enter a number between 1 and 20 to search for:", "Exceldemy")
If lookup_num = "" Then End
If Not IsNumeric(lookup_num) Then GoTo Input_Box
If lookup_num < 1 Or lookup_num > 20 Then GoTo Input_Box
msg = "Your value, " & lookup_num & ", was not found in the array."
For x = 1 To UBound(My_Array)
If My_Array(x) = lookup_num Then
msg = "Your value, " & lookup_num & ", was found at position " & x & " in the array."
Exit For
End If
Next x
MsgBox _
msg, vbOKOnly, "Exceldemy"
End Sub

• Press F5 to run the code.
• Enter a value (e.g., 5) when prompted.
• Click OK.

• The message box will indicate whether the value was found and its position in the array.

• Go to the View tab and select Immediate Window (or press CTRL+G).

• You’ll see that the first occurrence of 5 is in the 12th position in the array.

Read More: How to Check If Array Is Empty with VBA in Excel

Method 2 – Using Excel VBA to Get Lookup Value in Two-Dimensional Array

2.1 Utilizing the Lookup Worksheet Function

We want to find the quantity of Apricot.

• Open Microsoft Visual Basic for Applications (VBA).
• Double-click on the Lookup sheet (Sheet3) where we’ll work.

• In the code module, paste the following code:

Sub LOOKUP_Value()
Range("C17").Value = WorksheetFunction.Lookup(Range("B17").Value, _
Range("B5:B14"), Range("D5:D14"))
End Sub

Here,

• • C17 is the output cell where we’ll get the relevant Quantity of a Product stated in cell B17.
  • The Lookup WorksheetFunction takes the value of cell B17 as lookup_value, cells in the B5:B14 range as lookup_vector, and cells in the D5:D14 range as the result_vector.

• Run the code (click the Run icon or press F5).

• Return to the worksheet. You’ll find the quantity of Apricot (entered in cell B17) displayed as 20 in cell C17.

2.2 Applying VLookup Worksheet Function

We want to find the Customer linked to the product Kiwi.

• Open VBA again.
• Double-click on the VLOOKUP sheet (Sheet4).

• In the code module, paste this code:

Sub VLOOKUP_Value()
Dim Customer As String
Customer = Application.VLookup(Range("B17").Value, Sheet4.Range("B5:D14"), 2)
Range("C17").Value = Customer
End Sub

• Run the code.

• Return to the worksheet. You’ll see the customer’s name Hardy for the product Kiwi (entered in cell B17) displayed in cell C17.

Read More: Excel VBA: Determine Number of Elements in Array

Finding the Position of a String in an Array

We want to find Lemon/String in the list of Products/Array.

• Open VBA following the steps from Method 1.
• Double-click on the String sheet (Sheet5).

• In the code module, enter the following code:

Sub find_string()
Range("E5").Value = WorksheetFunction.Match(Range("D5").Value, _
Range("B5:B14"), 0)
End Sub

• Run the code.

• Return to the worksheet. Cell E5 will show the position of the string Lemon (entered in cell D5) as 9 in the array.

Practice Section

We have provided a Practice section like the one below on each sheet on the right side.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• Excel VBA to Populate Array with Cell Values
• VBA Array Size Limit in Excel
• VBA to Get Array Dimensions in Excel