To Use Hlookup with formula and VBA code

Hlookup formula in Microsoft Excel is to find something in table or range by columns. In this example we refer to table monthly score in Sheet1.

Table in Sheet1: Base on monthly we need to find score in

• Range("B2:M3)
  
• Row index = 2

Formula = HLOOKUP(lookup_value,table_array,row_index_num,range_lookup).

In Sheet2 we have another table without score, now we have to lookup this value from Sheet1 table by using formula.

Fill this formula into Range("C2") = HLOOKUP(B2,Sheet1!$B$2:$M$3,2,FALSE).

• B2 is lookup_value
• Sheet1!$B$2:$M$3 is table_array but $ sign to fix the table when drag down.
• 2 is row_index_num
• FALSE is range_lookup (FALSE - Exact Match, TRUE - Approximate match)

VBA code:

Option Explicit
Sub HLookUPExample()

    Dim i As Integer
    With ActiveSheet
        i = 2
        Do
            .Range("C" & i) = Application.HLookup(.Range("B" & i), ActiveWorkbook.Sheets("Sheet3").Range("$B$2:$M$3"), 2, False)
        i = i + 1
        Loop While .Range("A" & i) <> ""
    End With
    
End Sub

Note:

• Make sure Sheet2 is selected or activated.
• If your table is located in another workbook then change ActiveWorkbook to Workbooks("MyBookName.xls") but ensure this workbook is open.

Microsoft Reference - Hlookup function

Done.