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

Get FREE Advanced Excel Exercises with Solutions!

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.

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:

  • At the very beginning, go to the Developer tab.
  • After that, select Visual Basic on the Code group.

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

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


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

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

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


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.

Practice Section


Download Practice Workbook

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

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

 

 

ExcelDemy
Logo