, ,

Mengenal XLOOKUP dan Cara Membuatnya di Versi Excel Lama


Rumus Xlookup

XLOOKUP adalah formula baru dari Excel 365 bagi yang terdaftar ke dalam office Insider, formula ini adalah update untuk formula sebelumnya yang sangat banyak digunakan oleh pengguna Excel yaitu VLOOKUP dan HLOOKUP. XLOOKUP hadir untuk menyempurnakan kekurangan dari rumus Vlookup dan teman-temannya. Seperti yang kita tahu saat ini banyak yang lebih memilih menggunakan Index dan Match daripada Vlookup karena Index Match Membawa Table Array yang lebih sedikit sehingga lebih ringan untuk data yang banyak.

Mungkin karena hal ini, banyaknya user menggunakan Index dan Match untuk Solusi rumus Vlookup, akhirnya pada Update Excel Tahun 2019 ini, Microsoft Excel menghadirkan rumus xlookup. Dimana Rumus XLOOKUP ini adalah seperti rumus Index Match yang dibungkus didalam rumus Vlookup.

“XLOOKUP is more powerful than INDEX/MATCH and more approachable than VLOOKUP.”

Joe McDaid – Excel Senior Program Manager

Untuk lebih jelas lagi mengenal rumus baru Xlookup, berikut ini adalah sintaksis dari Rumus Xlookup.

 Sintaksis 
=XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode]) 

Penjelesan singkat tentang Sintak diatas

ArgumentDescription
lookup_valueNilai Acuan
lookup_arrayKolom Atau Range dimana Nilai Acuan Berada
return_arrayKolom Atau Range untuk Nilai yang dicari
match_modeIni Optional untuk mode Pencarian.
0 – Sama Persis. Jika tidak ditemukan akan #N/A. (default)

-1 – Sama persis. Jika tidak ada akan menggunakan Nilai terkecil berikutnya

1 – Sama Persis. Jika tidak ada akan menggunakan Nilai terbesar berikutnya

2 – Untuk wildcard match seperti *, ?, dan ~
search_modeIni Optional Untuk Metode Pencarian
1 – Pencarian dimulai dari Items Pertama. (default).

-1 – Pencarian dibalik, dimulai dari Item terkahir.

2 – Metode Pencarian binary di kolom lookup_array harus berurutan jika tidak akan invalid.

-2 – Metode Pencarian binary pada kolom lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

Dalam penggunaan rumus yang sederhana, cara kerja xlookup ini mirip sekali dengan index + match. Rumus xlookup hanya membutuhkan 3 kriteria, yaitu:
1. Nilai yang akan di cari
2. Range atau Kolom dimana Nilai No 1 berada
3. Range atau Kolom dimana Nilai Balik yg diharapkan berada

Jika dibandingkan dengan Vlookup, Penggunaan Xlookup secara sederhana ini tidak lagi membutuhkan column_number dan juga True/False untuk metode pencarianya. jadi sangat simple!. Berikut adalah contoh penggunaan rumus Xlookup.

Rumus XLookup Untuk Versi Excel Lama

Bagi yang masih terkendala mendapatkan excel versi terbaru, dan masih ingin bertahan dengan versi Excel lamanya, tidak perlu khawatir untuk bisa mecicipi rumus Xlookup ini. Karena rumus Xlookup ini bisa kita tambahkan ke dalam versi Excel lama dengan bantuan Macro. Rumus macro dibawah ini sudah dibuat sedemikian rupa agar berprilaku mirip dengan rumus xlookup yang ada di Excel terbaru.

Agar bisa menggunakan rumus Xlookup ini ada dua cara yang bisa dilakukan, yang pertama bisa menggunakan macro ini sebagai UDF. Untuk bisa menggunakan rumus ini sebagai UDF, Copy script dibawah ini kedalam Module di Visual Basic Editor.

 Visual Basic Editor 
Function XLOOKUP(rng1 As Variant, rng2 As Range, rng3 As Variant, Optional arg1 As Variant, Optional arg2 As Variant) As Range
Rem Auth : ExcelNoob.com
If IsMissing(arg1) Then arg1 = 0
If IsMissing(arg2) Then arg2 = 0
Dim rsult As Variant 'Untuk Hasil array Akhir
Dim r2width As Integer: r2width = rng2.Columns.Count
Dim r3width As Integer: r3width = rng3.Columns.Count
Dim rtnHeaderColumn As Boolean: rtnHeaderColumn = r2width > 1
If r2width > 1 And r2width <> r3width Then
   XLOOKUP = CVErr(xlErrRef)
   Exit Function
End If
Dim srchVal As Variant: srchVal = rng1.Value 'Nilai yg dicari'
Dim sIndex As Double: sIndex = rng2.Row - 1 
Dim n As Long 'for array loop
If (arg1 <> 2 And VarType(rng1) = vbString) Then srchVal = Replace(Replace(Replace(srchVal, "*", "~*"), "?", "~?"), "#", "~#") 'untuk wildcard switch'
'-----------------------'
Dim srchType As String
Dim matchArg As Integer
Dim lDirection As String
Dim nextSize As String
Select Case arg1 
    Case 0, 2
        If arg2 = 0 Or arg2 = 1 Then
            srchType = "im"
            matchArg = 0
        End If
    Case 1, -1
        nextSize = IIf(arg1 = -1, "s", "l") 
        If arg2 = 0 Or arg2 = 1 Then
            srchType = "lp"
            lDirection = "forward"
        End If
End Select
Select Case arg2 
    Case -1
        srchType = "lp": lDirection = "reverse"
    Case 2
        srchType = "im": matchArg = 1
    Case -2
        srchType = "im": matchArg = -1
End Select
If srchType = "im" Then 
    If rtnHeaderColumn Then
        Set XLOOKUP = rng3.Columns(WorksheetFunction.Match(srchVal, rng2, matchArg))
    Else
        Set XLOOKUP = rng3.Rows(WorksheetFunction.Match(srchVal, rng2, matchArg))
    End If
    Exit Function
Else  
    Dim vArr As Variant: vArr = IIf(rtnHeaderColumn, WorksheetFunction.Transpose(rng2), rng2) 
    Dim nsml As Variant: ' nsmal - next smallest value
    Dim nlrg As Variant: ' nlrg - next largest value
    Dim nStart As Double: nStart = IIf(lDirection = "forward", 1, UBound(vArr))
    Dim nEnd As Double: nEnd = IIf(lDirection = "forward", UBound(vArr), 1)
    Dim nStep As Integer: nStep = IIf(lDirection = "forward", 1, -1)
        For n = nStart To nEnd Step nStep
            If vArr(n, 1) Like srchVal Then Set XLOOKUP = IIf(rtnHeaderColumn, rng3.Columns(n), rng3.Rows(n)): Exit Function 
            If nsml < vArr(n, 1) And vArr(n, 1) < srchVal Then 
                Set nsml = rng2.Rows(n)
            End If
            If vArr(n, 1) > srchVal And (IsEmpty(nlrg) Or nlrg > vArr(n, 1)) Then 
                Set nlrg = IIf(rtnHeaderColumn, rng2.Columns(n), rng2.Rows(n))
            End If
        Next
End If
If arg1 = -1 Then 
    Set XLOOKUP = rng3.Rows(nsml.Row - sIndex)
ElseIf arg1 = 1 Then 
    Set XLOOKUP = rng3.Rows(nlrg.Row - sIndex)
End If
End Function

Untuk cara kedua, script diatas bisa dibuat menjadi addin. Untuk mengaktifkan rumus Xlookup tinggal install addin yang telah dibuat tersebut. Tenang saja, jika mengalami kesulitan dalam membuat addin, excelnoob telah membuat addin Xlookup sehingga teman-teman excelnoob tinggal pake saja. Untuk mendownload addin Xlookup silahkan kunjungi halaman download.