Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Find Lookup Value in Array in Excel VBA (2 Easy Ways)

Most likely, you’re familiar with lookup value in an array in Excel. What if you need to do the same task using Excel VBA? Sounds interesting. Sometimes we need to find a particular value from an array in Excel. In this case, we could use VBA code instead of formula in excel. Here, we will take you through 2 easy and convenient methods of getting a lookup value in an array using VBA in Excel.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


2 Methods to Find Lookup Value in Array Using Excel VBA

For clarification, 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

Remember that we’ll use this dataset for Method 2. Now, we’ll find the lookup value in this array using different approaches. So let’s explore them one by one.


1. Using Excel VBA to Get Lookup Value in One Dimensional Array

In our foremost method, we’ll create a one-dimensional array first with VBA code. Then, we’ll find a value from this array. The entire process will be done within the Code Module. Let’s explore the method step by step.

📌 Steps:

Using Excel VBA to Get Lookup Value in One Dimensional Array

  • Immediately, the Microsoft Visual Basic for Applications window appears.
  • Here, proceed to the Insert tab.
  • Then, select Module from the list.

Inserting Code Module

  • Instantly, the Code Module pops out.
  • Later, paste the following code into that 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

VBA Code to Get Lookup Value in One-Dimensional Array

Code Breakdown

Sub Lookup_Array()
Dim My_Array(20) As Integer
Dim x As Integer
Dim lookup_num As Variant
Dim msg As String
This section states the sub-procedure name. It also declares an array and some variables. Here, lookup_num is the value that we’ll search in the My_Array array.

For x = 1 To 20
My_Array(x) = Int(Rnd * 20)
Debug.Print My_Array(x)
Next x
This section creates an array consisting of 20 random numbers between 1 and 20. Thus, we can see the result in the Immediate Window.

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.”
In this part, it takes the input from the user and then checks if the number is within the limits. If not it will ask for input again. And if the value is within 1 to 20 but not present in the array, then it will show a message.

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”
Here, we’re running a For loop in the array to find the position of the entered value in that array. And the result will be shown in MsgBox.

End Sub
It makes the end of the sub-procedure.

  • Now, run the code by pressing F5 on the keyboard.
  • Suddenly, an input box appears before us.
  • Here, we input 5 to search for.
  • Lastly, click OK.

Write down number in input box

  • Immediately, we can see a MsgBox saying that Your Value, 5, was found at position 12 in the array.
  • Hence, click OK.

Lookup Value in Array Showing in Msgbox

  • To verify the result, we’ll open the Immediate Window.
  • At first, jump to the View tab.
  • Then, select Immediate Window.
  • Alternatively, press CTRL+G to replicate the task.

Opening Immediate Window

In the Immediate Window, we can 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 Create an Array in Excel VBA (4 Ideal Methods)


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

In this section, we’ll use a two-dimensional array to solve the problem. The array is shown before. So, let’s see the process in detail.


2.1 Utilizing Lookup WorksheetFunction

Here, we want to find the quantity of the corresponding product. Let’s see the image below.

Utilizing LOOKUP to Get Lookup Value in Array in Excel VBA

In the Product column, we entered the product name Apricot in cell B17. And we’ll fetch its correlative Quantity in cell C17. It’s simple & easy, just follow along.

📌 Steps:

  • Firstly, open Microsoft Visual Basic for Applications like before.
  • Then, double-click on Sheet3 (Lookup) as we’re working on this sheet now.

Bringing up Code Module

  • Instantly, the code module opens.
  • Secondly, paste the following code into the module.
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

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.

  • Consequently, click on the Run icon or press F5 on the keyboard to run the code.

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

  • After that, return to the worksheet and you’ll get the Quantity of Apricot as 20 in cell C17.

Lookup Value in Array in Excel VBA

Read More: Excel VBA: Determine Number of Elements in Array (4 Examples)


2.2 Applying VLookup WorksheetFunction

In this area, we’ll find out the Customer name of a certain Product. First, see the following image.

Applying VLOOKUP to Get Lookup Value in Array in Excel VBA

Here, we want to discover the Customer of Kiwi. So, without further delay, let’s see how to do it using Excel VBA.

📌 Steps:

  • First of all, open Microsoft Visual Basic for Applications like before.
  • After that, double-click on Sheet4 (VLookup) because we’re working on this sheet currently.

  • All of a sudden, the code module opens.
  • Then, paste the following code into the module.
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

Running the Sub-procedure

  • Next, reappear to the worksheet. Here, you can see the Customer name Hardy of the Product Kiwi in cell C17.

Applying VLOOKUP to Get Lookup Value in Array in Excel VBA

Read More: How to Use Arrays Instead of Ranges in Excel VBA


Excel VBA to Find String in Array

In this field, we’ll obtain the position of a string in a certain array. Let’s observe the image first.

VBA Find String in Array

Here, our selected string is Lemon. We’ll test if the text string is present inside the array and also try to know its position. So, without further delay, let’s dive in!

📌 Steps:

  • Primarily, follow the steps of Method 1 to open Microsoft Visual Basic for Applications window.
  • Then, double-click on Sheet5 (String).

  • At this moment, the code module shows up.
  • Straightaway, write down the following code in the module.
Sub find_string()
Range("E5").Value = WorksheetFunction.Match(Range("D5").Value, _
Range("B5:B14"), 0)
End Sub

Writing Code into the Code Module

  • Therefore, run the code like before.

  • Also, return to the worksheet.
  • Then, see cell E5 having the position of the string Lemon as 9 in the array.

Using MATCH Function to Find String in Array in VBA

Read More: How to Declare Array in Excel VBA (3 Easy Ways)


Practice Section

To practice by yourself, we have provided a Practice section like the one below in each sheet on the right side. Please do it by yourself.

Practice Section


Conclusion

This article provides easy and brief solutions to get lookup value in an array using Excel VBA. Don’t forget to download the Practice file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website Exceldemy to explore more.


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo