Filter with Exact Match(Case-Sensitive)

Suppose that you have a table consisting of a few cells with few values, and you want to filter out the set of records with the exact match concerning case sensitivity. You might take it easy and would prefer to manually filter out the desired case-sensitive match into another table without any need for the formula; then congratulations because you are thinking right.

But let me add that it would be a big deal while dealing with a bulk of data in the table, and then doing this bulky task manually would be a foolish decision.

But there isn’t any need to worry about it because after carefully reading this article, filtering out the set of records with the exact matches will be a piece of cake for you.

filter data with Exact match1

So let’s get straight into it!

 General Formula:

You can use the FILTER function in combination with the EXACT function to choose data records based on a case-sensitive match. The formula in F2 is written as follows:

=FILTER(total_data,EXACT(Filter_range,Filter_value))

filter data with Exact match1

Let’s See How This Formula Works:

For example, suppose you got a task in which their table consists of Four columns (e.g., product, Employee, Region and Sales ) from which you need to filter out the data concerning the case sensitivity; Now let’s analyze how to write the formula and how this formula would do it.

As to extract the whole row of the product “EXCEL” respecting upper and lower case, so according to these requirements, we would write the formula as follows:

filter data with Exact match1

=FILTER(A2:D9,EXACT(A2:A9,"EXCEL"))

In the above formula, the FILTER function is used to get data based o the exact match. The array parameter is A2:D9, and it contains all of the data without the headers. The Include parameter is an EXACT function-based expression:

=EXACT(A2:A9,"EXCEL")

The EXACT function compares two case-sensitive text strings. If the two strings are identical, EXACT returns TRUE. If the two strings are not identical, EXACT returns FALSE, EXACT gives an array of 8 results, as seen below:

filter data with Exact match1

It’s worth noting that the location of TRUE values in this array correlates to the rows with the product “EXCEL”.

This array was returned straight to the FILTER function as the include parameter. FILTER filters the range A2:D9 using the array and returns the Four rows where the product is “EXCEL“. Rows with the product “excel” are excluded.

Following are the two other ways to Filter out data with an exact match in MS Excel:

Using Excel’s Advanced Filter tool to Filter out data with an exact match

According to the given Example, we have the following range of data, and we want to do now is filter the cells whose content is “EXCEL”. Please do the following:

Step1: In the worksheet, create the criterion, and then to input the column header name that you want to filter, kindly use the Advanced Filter utility moreover this formula: =”=EXCEL” (EXCEL is the exact text you want to filter) underneath the herder cell, and click the Enter key, as seen in the screenshot:

filter data with Exact match1

Step2: Then, as seen in the screenshot, go to Data > Advanced.

filter data with Exact match1

Step3: In the Advanced Filter dialogue box, choose Filter the list, in-place under the Action, and then specify the listed range to filter and the criterion range to filter based on, as shown in the screenshot:

filter data with Exact match1

Step4: Then, after clicking the OK button, the precise text that you want will be filtered, as seen in the picture below:

filter data with Exact match1

Using the Custom Filter option to Filter out data with an exact match

In reality, the Auto Filter can also assist you in achieving the desired outcome.

Step1: Choose the data range from which you wish to filter precise text.

Step2:To display the arrow button, go to Data > Filter.

Step3:Then, in the lower right corner of the cell, click the arrow button, and then pick Text Filters > Custom Filter, as seen in the screenshot:

filter data with Exact match1

Step4:In the Custom AutoFilter dialogue box that appears, choose equals from the drop-down list and input the text criteria that you wish to filter on, as shown in the screenshot:

filter data with Exact match1

Step5:Finally, click the OK button to filter the exact text you want.

filter data with Exact match1

Related Functions

  • Excel Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
  • Excel EXACT function
    The Excel EXACT function compares if two text strings are the same and returns TRUE if they are the same, Or, it will return FALSE.The syntax of the EXACT function is as below:= EXACT (text1,text2)…
Related Posts

Break ties with helper COUNTIF and column

Suppose you got a task to adjust the values that contain the ties; what would be your first attempt to break the ties of the given value? If you are wondering about doing this task manually, let me add that ...

Calculate Total Cost with Excel VLOOKUP Function

In today's article we will show you how to calculate the total cost for a given weight using the Excel VLOOKUP function. This function will help us to find the appropriate unit price for that weight and then we can ...

Excel Formulas To Calculate The Bond Valuation

Assume that you've been assigned a task of calculating bond valuation; so if you are new to Ms Excel or do not have much experience with it, then I am pretty sure about it that doing this task manually might ...

Calculate Cumulative Totals with Excel SUM Function

Today, through a simple example, we will show you how to use one of the most common-used Mathematical functions in excel, the SUM function, to add up the sum. In our daily life, we keep an account of what we ...

BMI Calculation Formula In Ms Excel

You might have come across a task in which you were assigned to make BMI calculations of the supplied numbers, and you may be looking for an efficient approach to accomplish this process rather than doing BMI calculations manually, by ...

Calculate The Period of Loan or Investment in Excel

In our daily life, most of us will invest or take a loan, so we need to master some simple financial functions to calculate the period needed for loans or investments. If you work as an accountant, you need to ...

Working Time Calculation Based on Timesheets

In the office, a special machine record the time when you start working (clock in office) and when you finish working (clock out of office). We can calculate the total working time by subtracting the end working time from the ...

Sort/Rank Numeric Values with Duplicate Values Exist

Excel built-in RANK function can sort a set of values. If there are duplicate numbers, then the rank number is also duplicated. See the following example: There are two numbers “100” in range A2:A9, they are both the third largest ...

Calculate Days Open in Excel

If you want to know how to Calculate days in Excel, there are some formulas that you can use to do so. For example, you can use the DAYS function in Excel to find the number of days between two ...

Calculate Grades With VLookup in Excel

Why Should You Calculate Grades With VLookup Excel? If you're looking for a simple way to find out a student's grades, VLOOKUP Excel can do the trick. This function uses a lookup table to find the values and sort them ...

Sidebar