How to Use Excel VBA to Lookup Value in Range (5 Examples)

VBA Breakdown:

• This code starts with the Sub statement and the name of the subroutine, “Lookup_Value().”

Sub Lookup_Value()

• Now we are Introducing Variables: The cell where the lookup result will be shown is stored in the variable ResCell As Range. LookupValCell As Range: This variable keeps the lookup value’s cell. LookupVec As Range: This variable stores the lookup vector’s range. ResVec As Range: This variable keeps the result vector’s range.

Dim ResCell As Range
Dim LookupValCell As Range
Dim LookupVec As Range
Dim ResVec As Range

• The Set command assigns each variable to the appropriate cell or range. The lookup is carried out using the Lookup function. The lookup value is represented by the LookupValCell variable, the lookup vector by the LookupVec variable, and the result vector by the ResVec variable. The ResCell variable holds the lookup’s outcome.

Set ResCell = Range("G5")
Set LookupValCell = Range("F5")
Set LookupVec = Range("C5:C14")
Set ResVec = Range("D5:D14")
ResCell = WorksheetFunction.Lookup(LookupValCell, LookupVec, ResVec)

• This VBA macro ends with an End statement.

End Sub
			

• Click on Run to see the output.

VBA Breakdown:

• This code starts with the Sub statement and the name of the subroutine, “Find_Marks().”

Sub Find_Marks()

• The code begins by declaring several variables, including studentID, exam, result, lookupRange, examIndex, Lookupvalue, and tableArray. The exam is an integer, while the result is a text (a range).

    Dim studentID As Integer
    Dim exam As String
    Dim result As Variant
    Dim lookupRange As Range
    Dim examIndex As Variant
    Dim Lookupvalue As Variant
    Dim tableArray As Range

• The user must enter the student ID and exam name into the InputBox function.

    studentID = InputBox("Enter the student ID:")
    exam = InputBox("Enter the exam name:")

• The lookupRange variable is set to the table row containing the exam names.

    Set lookupRange = Worksheets("Sheet2").Range("B4:F4")

• The Match function sets the examIndex variable to the provided exam’s column index.

    examIndex = Application.Match(exam, lookupRange, 0)

• The user-entered student ID is used as the value for the Lookupvalue variable. The full table’s data range is contained in the tableArray variable.

   Lookupvalue = studentID
   Set tableArray = Worksheets("Sheet2").Range("B4:F7")

• Using the lookupValue and examIndex variables, the Index function searches the table for the student’s grades.

result = Application.Index(tableArray, Application.Match(lookupValue, tableArray.Columns(1), 0), examIndex)

• This VBA macro ends with an End statement.

End Sub

VBA Breakdown:

Sub Finding_birthplace()

• Firstly, this VBA  macro begins with the sub-procedure Finding_birthplace, which is used to locate a person’s birthplace by searching for their name in a table.

Dim ws_1 As Worksheet, ws_2 As Worksheet
Dim Finding_birthplaceAs Variant

• Secondly, we use the Dim statement to declare three variables: ws_1 and ws_2 as Worksheet objects and Finding_birthplace as a Variable data type.

Set ws_1 = Worksheets("Birth_place")
Set ws_2 = Worksheets("VBA")

• We apply the Set statement to assign the Birth_place and VBA worksheets to the ws_1 and ws_2 variables, respectively.

On Error Resume Next

• Then, using the On Error Resume Next statement, we ignore any runtime errors that may occur during the macro’s implementation.

 Finding_birthplace = Application.WorksheetFunction.VLookup(ws_2.Range("B5"), _
 ws_1.Range("B5:C11"), 2, False)

• We set the Finding_birthplace variable to assign the result of the VLOOKUP function, which finds the value in cell B5 of the VBA worksheet in the table in the Birth_place worksheet’s B5:C11 range. The function returns the value in the second column of the table (column C) that corresponds to the matching value in column B.

On Error GoTo 0

• Here, we apply the, On Error GoTo 0 statement to turn off the error handling.

 If Else statement
           If (IsEmpty(Finding_birthplace)) Then
                  ws_2.Range("E5").Formula = CVErr(xlErrNA)
           Else
                 ws_2.Range("E5").Value = Finding_birthplace
          End If

• We apply the If Else statement to check if the Finding_birthplace variable is empty. If it is, the code assigns the #N/A error value to cell E5 of the VBA worksheet using the CVErr function. If it is not empty, the code assigns the value of the Finding_birthplace variable to cell E5.

End Sub

• The Sub procedure ends with the End Sub statement.

VBA Breakdown:

• This code creates a VBA method called Multiple_Match that accepts three arguments: Colindex, LookupVal, and RCell. (an integer). A comma-separated list of values in the column designated by the Colindex will be returned after this function has searched for instances of the LookupVal within the RCell range.

Public Function Multiple_Match(ByVal LookupVal As String, ByVal RCell As Range, ByVal Colindex As Integer) As Variant

• Cell (a range) and ResultStr (a string) are declared as local variables. Then, it configures error handling to detect any mistakes that might happen when accessing cell values.

Dim Cell As Range
Dim ResultStr As String
On Error GoTo Correction

• The code then uses a For Each loop to iterate through each cell in the RCell range.

For Each Cell In RCell

• It determines whether the value in each cell corresponds to the LookupVal. The function determines whether the relevant column value (defined by the Colindex) is not empty if the value in the cell matches the LookupVal. The function checks to see if the value hasn’t previously been used in the ResultStr if the column value isn’t empty. The function appends the value to the ResultStr string variable if it hasn’t already been there. Otherwise, it removes it.

  If Cell.Value = LookupVal Then
            If Cell.Offset(0, Colindex - 1).Value <> "" Then
                If Not Result_String Like "" & Cell.Offset(0, Colindex - 1).Value & "" Then
                ResultStr = ResultStr & ", " & Cell.Offset(0, Colindex - 1).Value
                End If
            End If
        End If
    Next Cell

• The function checks every cell in the RCell range before removing the leading comma and space from the ResultStr and returning it as the function’s output value.

Multiple_Match = LTrim(Right(ResultStr, Len(ResultStr) - 1))
Exit Function

• If an error occurs while the function is being executed, the output value is set to an empty string, and the error handling jumps to the Correction label.

Correction:
Multiple_Match = ""
End Function

VBA Breakdown:

Private Sub ComboBox1_Change()
'Declaring Variable
Dim i
'Using CountA worksheet function
i = Application.WorksheetFunction.CountA(ActiveSheet.Range("B:B"))

• We define the first subprocedure, “ComboBox1_Change()”. Secondly, we declare a variable “i“. Then, it counts the number of cells that contain data in column B of ActiveSheet using the COUNTA worksheet function and assigns the value to “i“.

For j = 1 To 4
  Me("Textbox" & j).Value = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, Sheet2.Range("B" & 2, "F" & i), j + 1, 0)
Next j

• The For loop runs for four iterations. Each iteration uses the VLOOKUP function to look up the selected name in the data range. It returns the column value corresponding to the current iteration plus one (starting with column C). The value is then assigned to the corresponding TextBox (named “Textbox1” to “Textbox4“).

• Private Sub UserForm_Initialize()
  Me.ComboBox1.RowSource = "Name"

  We will define a sub-procedure “UserForm_Initialize()“.

• It sets the RowSource property of the ComboBox to “Name“, which means that the ComboBox gets its list of names from a named range called “Name“.

End Sub

• This VBA macro ends with an End statement.