How to Use the Excel SearchInRange VBA Function for Efficient Search Across Ranges
Introduction:
If you work with large datasets in Excel, you know how difficult it can be to find specific data within a range. The SearchInRange VBA function is an excellent tool to help you quickly search for a number, text, or partial match across any specified range of cells. In this blog post, we’ll show you how to set up and use this custom Excel function, which offers flexibility in exact match, partial match, and case sensitivity.
What is the SearchInRange VBA Function?
The SearchInRange function is a custom VBA (Visual Basic for Applications) function that allows users to search for any value (text, numbers, or part of a string) within a specified range of cells. It’s ideal for searching large datasets, as it provides options to match values exactly, partially, or based on case sensitivity.
Key Features:
- Exact Match Search: Find an exact match for a given value.
- Partial Match Search: Search for a value within text or numbers.
- Case Sensitivity Options: Specify whether or not the search should respect letter case.
Why Use the SearchInRange Function?
Searching through a large Excel dataset manually can be time-consuming and inefficient. With SearchInRange, you can streamline your workflow by automating the search process. Whether you need to search for specific values, partial matches, or perform a case-sensitive search, this function makes it simple.
How to Install the SearchInRange VBA Function in Excel
Follow these steps to enable the SearchInRange function in Excel:
- Open Excel and press
Alt + F11to open the VBA Editor. - Go to Insert > Module.
- Copy and paste the
SearchInRangefunction code into the module window. - Close the VBA Editor and save your workbook as a Macro-Enabled Workbook (
.xlsm).
How to Use the SearchInRange Function in Excel
The function can be used just like any other Excel function in your worksheet. Here’s how to apply it:
1. Exact Match (Case-Insensitive)
Search for an exact match in the specified range:
=SearchInRange("HELLO", A1:C3, TRUE, FALSE)This searches for the word “HELLO” in the range A1:C3 without considering case sensitivity. If a match is found, the function will return the value of the cell.
2. Exact Match (Case-Sensitive)
If you want to match the case exactly, use the following formula:
=SearchInRange("HELLO", A1:C3, TRUE, TRUE)This will return Not Found if the case doesn’t match exactly.
3. Partial Match (Case-Insensitive)
To search for a partial match, use this formula:
=SearchInRange("hello", A1:C3, FALSE, FALSE)This will return hello123, as it matches the part of the text “hello”.
4. Invalid or Empty Search Value
If the search value is empty or invalid, the function will return "Invalid Search Value".
Example of Data and Usage
Let’s take a quick look at a sample dataset:
| A | B | C |
|---|---|---|
| Apple | Orange | Banana |
| Cat | Dog | Fish |
| HELLO | hello123 | World |
Formulas and Results:
| Formula | Result | Description |
|---|---|---|
=SearchInRange("HELLO", A1:C3) | HELLO | Exact match, case-insensitive |
=SearchInRange("hello", A1:C3, TRUE, TRUE) | Not Found | Exact match, case-sensitive (no match) |
=SearchInRange("hello", A1:C3, FALSE, TRUE) | hello123 | Partial match, case-sensitive |
=SearchInRange("Ban", A1:C3, FALSE, FALSE) | Banana | Partial match, case-insensitive |
Conclusion
The SearchInRange function is a versatile and easy-to-use tool for efficiently finding data within Excel. Whether you’re dealing with exact values or partial matches, and no matter whether case sensitivity matters, this function can streamline your workflow and help you find the information you need faster.
