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
| Argument | Description |
|---|---|
| lookup_value | Nilai Acuan |
| lookup_array | Kolom Atau Range dimana Nilai Acuan Berada |
| return_array | Kolom Atau Range untuk Nilai yang dicari |
| match_mode | Ini 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_mode | Ini 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 EditorFunction 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.