@Rez Kautsar ...

Formula :

=IF(ISNA(VLOOKUP(test3.xlsx!B2;test2.xlsx!$B$2:$E$20;3;FALSE));"tidak ada";IF(VLOOKUP(test3.xlsx!B2;test2.xlsx!$B$2:$E$20;3;FALSE)>5000;"";VLOOKUP(test3.xlsx!B2;test2.xlsx!$B$2:$E$20;1;FALSE)))

dan

=IF(ISNA(VLOOKUP(test3.xlsx!B2;test2.xlsx!$B$2:$E$20;3;FALSE));"tidak ada";IF(VLOOKUP(test3.xlsx!B2;test2.xlsx!$B$2:$E$20;3;FALSE)>5000;" lebih"; "kurang"))

dapat diterjemahkan ke dalam sebuah prosedur VBA menggunakan fungsi yang sama. Berikut ini contohnya :

Public Function HasilVlookup(ByVal Acuan As Variant, ByVal Senarai As Variant, ByRef CariNama As Boolean) As Variant
    Err.Clear: On Error Resume Next
    
    '+-- IF(ISNA(VLOOKUP(test3.xlsx!B2, test2.xlsx!$B$2:$E$20, 3, FALSE))
    HasilVlookup = Application.WorksheetFunction.VLookup(Acuan, Senarai, 3, 0)
    
    If Err.Number <> 0 Then
        '+-- Blok IF pertama = ISNA
        HasilVlookup = "TIDAK ADA"
    Else
        '+-- IF(VLOOKUP(test3.xlsx!B2, test2.xlsx!$B$2:$E$20, 3, 0) > 5000
        If HasilVlookup > 5000 Then
            '+-- Blok IF kedua > 5000
            If CariNama Then
                '+-- Untuk formula pertama (cari nama)
                HasilVlookup = vbNullString
            Else
                '+-- Untuk formula kedua (cari kuantitas)
                HasilVlookup = "LEBIH"
            End If
        Else
            If CariNama Then
                '+-- Untuk formula pertama (cari nama)
                '+-- VLOOKUP(test3.xlsx!B2;test2.xlsx!$B$2:$E$20;1;FALSE)
                HasilVlookup = Application.WorksheetFunction.VLookup(Acuan, Senarai, 1, 0)
            Else
                '+-- Untuk formula kedua (cari kuantitas)
                HasilVlookup = "KURANG"
            End If
        End If
    End If

    Err.Clear: On Error GoTo 0
End Function

Sedangkan untuk :

... sama buat vlookupnya yang nyari rownya dinamis gitu ...

saya tidak paham maksudnya bagaimana. Terlampir contoh macro VBA untuk kedua formula di atas. Semoga sesuai.

Demikian.

@Rez Kautsar ...

Klo prosedur VBA yang dibuat akan digunakan sebagai user formula (seperti contoh di atas), baiknya gunakan saja kolom absolut (contoh : $B:$E) sebagai referensi range pencarian data, misalkan untuk kolom NAMA :

=HasilVlookup(test3.xlsx!$B2;test2.xlsx!$B:$E;TRUE)

dan untuk kolom QUANTITY :

=HasilVlookup(test3.xlsx!$B2;test2.xlsx!$B:$E;FALSE)

Tidak perlu repot memikirkan jumlah baris datanya. Excel sudah punya mekanisme yang lebih baik untuk menentukan jumlah baris data aktualnya. Kecuali, range data yang dirujuk alamatnya tidak lazim, misalkan dimulai dari baris ke-25, mungkin perlu dibuat baris script khusus untuk menentukan range datanya. Klo datanya dimulai dari baris ke-2 dan seterusnya, baiknya pake kolom absolut saja...

Tapi klo memang mau menggunakan macro VBA untuk menentukan range rujukan, bisa coba searching di internet, untuk menentukan baris akhir dari sebuah range. Ada banyak caranya. Contohnya, fungsi HasilVLOOKUP di atas direvisi menjadi :

Public Function HasilVLOOKUP2(ByVal Acuan As Variant, ByRef RangeData As Range, ByVal CariNama As Boolean, ByVal SkipBarisAwal As Boolean) As Variant
    Dim lBarisAkhir As Long
    Dim sAlamatRange As String
    
    Err.Clear: On Error Resume Next
    
    lBarisAkhir = RangeData.Cells(RangeData.Rows.Count, RangeData.Column).End(xlUp).Row
    
    If lBarisAkhir > RangeData.Row Then
        If SkipBarisAwal Then
            sAlamatRange = RangeData.Range(RangeData.Cells(RangeData.Row + 1, 0), RangeData.Cells(lBarisAkhir, RangeData.Columns.Count - 1)).Address
        Else
            sAlamatRange = RangeData.Range(RangeData.Cells(RangeData.Row, 0), RangeData.Cells(lBarisAkhir, RangeData.Columns.Count - 1)).Address
        End If
    Else
        HasilVLOOKUP2 = CVErr(xlErrRef)
        Exit Function
    End If    
    
    Set RangeData = RangeData.Parent.Range(sAlamatRange)
    
    '+-- IF(ISNA(VLOOKUP(test3.xlsx!B2, test2.xlsx!$B$2:$E$20, 3, FALSE))
    HasilVLOOKUP2 = Application.WorksheetFunction.VLookup(Acuan, RangeData, 3, 0)
    
    If Err.Number <> 0 Then
        '+-- Blok IF pertama = ISNA
        HasilVLOOKUP2 = "TIDAK ADA"
    Else
        '+-- IF(VLOOKUP(test3.xlsx!B2, test2.xlsx!$B$2:$E$20, 3, 0) > 5000
        If HasilVLOOKUP2 > 5000 Then
            '+-- Blok IF kedua > 5000
            If CariNama Then
                '+-- Untuk formula pertama (cari nama)
                HasilVLOOKUP2 = vbNullString
            Else
                '+-- Untuk formula kedua (cari kuantitas)
                HasilVLOOKUP2 = "LEBIH"
            End If
        Else
            If CariNama Then
                '+-- Untuk formula pertama (cari nama)
                '+-- VLOOKUP(test3.xlsx!B2;test2.xlsx!$B$2:$E$20;1;FALSE)
                HasilVLOOKUP2 = Application.WorksheetFunction.VLookup(Acuan, RangeData, 1, 0)
            Else
                '+-- Untuk formula kedua (cari kuantitas)
                HasilVLOOKUP2 = "KURANG"
            End If
        End If
    End If

    Err.Clear: On Error GoTo 0
End Function

Terlampir contohnya.

Demikian.

@Rez Kautsar...

Contohnya khan bisa dilihat dari file yang sudah terlampir di atas. Itu contoh UDF atau User Defined Function. Sama kayak fungsi formula biasa Excel, dipanggil dari sel Worksheet.

Demikian.