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 a 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.
How to Find Lookup Value in Array Using Excel VBA: 2 Simple Methods
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.
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.
- At the very beginning, go to the Developer tab.
- After that, select Visual Basic on the Code group.
- Immediately, the Microsoft Visual Basic for Applications window appears.
- Here, proceed to the Insert tab.
- Then, select Module from the list.
- 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
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.
- Immediately, we can see a MsgBox saying that Your Value, 5, was found at position 12 in the array.
- Hence, click OK.
- 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.
In the Immediate Window, we can 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
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.
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.
- 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
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.
- After that, return to the worksheet and you’ll get the Quantity of Apricot as 20 in cell C17.
2.2 Applying VLookup WorksheetFunction
In this area, we’ll find out the Customer name of a certain Product. First, see the following image.
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
- Repeatedly, click on the Run icon.
- Next, reappear to the worksheet. Here, you can see the Customer name Hardy of the Product Kiwi in cell C17.
Read More: Excel VBA: Determine Number of Elements in Array
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.
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 the 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
- 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.
Practice Section
To practice by yourself, we have provided a Practice section like the one below on each sheet on the right side. Please do it by yourself.
You may download the following Excel workbook for better understanding and practice yourself.
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.
Related Articles
- Excel VBA to Populate Array with Cell Values
- VBA Array Size Limit in Excel
- VBA to Get Array Dimensions in Excel
