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.