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

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.

excel vba lookup value in array

Method 1 – Create a One-Dimensional Array:

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

Using Excel VBA to Get Lookup Value in One Dimensional Array

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

Inserting Code 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
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

VBA Code to Get Lookup Value in One-Dimensional Array

Code Breakdown

    • The Lookup_Array sub-procedure creates a one-dimensional array called My_Array with 20 random integers between 1 and 20.
    • An input box prompts the user to enter a number to search for.
    • The code checks if the input is valid (within the range of 1 to 20) and whether it exists in the array.
    • A message box displays the search result.
  • Press F5 to run the code.
  • Enter a value (e.g., 5) when prompted.
  • Click OK.

Write down number in input box

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

Lookup Value in Array Showing in Msgbox

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

Opening Immediate Window

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

Using Excel VBA to Get Lookup Value in One Dimensional 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.

Utilizing LOOKUP to Get Lookup Value in Array in Excel VBA

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

Bringing up Code Module

  • 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

Writing Code to Find Lookup Value in Array in Excel VBA


    • 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).

Running the Code to Get Lookup Value in Array in Excel VBA

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

Lookup Value in Array in Excel VBA

2.2 Applying VLookup Worksheet Function

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

Applying VLOOKUP to Get Lookup Value in Array in Excel VBA

  • 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

Writing VBA Code

  • Run the code.

Running the Sub-procedure

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

Applying VLOOKUP to Get Lookup Value in Array in Excel VBA

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.

VBA Find String in Array

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

  • In the code module, write the following code:
Sub find_string()
Range("E5").Value = WorksheetFunction.Match(Range("D5").Value, _
Range("B5:B14"), 0)
End Sub

Writing Code into the Code Module

  • 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.

Using MATCH Function to Find String in Array in VBA

Practice Section

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

Practice Section

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

Get FREE Advanced Excel Exercises with Solutions!


Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF