## Dataset Overview

We have the following sample dataset which we are going to use to demonstrate the 7 methods. The dataset contains columns for **ID** and **Name**. It is the information of people working in a company. In addition, the database contains some case-sensitive values.

**Method 1 – Using the EXACT Function**

The **EXACT** function is the shortest way to compare two sets of text for exactness. It returns a Boolean value (**True** or **False**) based on whether the text match exactly. Let’s say we want to compare values in cell **C8** and **C10** of our dataset. The formula would be:

`=EXACT(C8,C10)`

However, even if the values match, the result might be **FALSE** due to case differences. For example, “**John**” and “**john**” would yield **FALSE**.

You can also check for other values. Let’s say we want to compare the cells, **C5 **and **C7. **

The formula will look like:

`=EXACT(C5,C7)`

Due to case-sensitive issues, the result will also be false.

**Method 2 – Nested OR and EXACT Functions**

To compare values in a range, create a new column for checker values. Then use nested **OR** and **EXACT** formulas. For instance:

`=OR(EXACT(D5,C10))`

**or**

`=OR(EXACT(D6,C8))`

**Notice**: *This formula checks if the checker value matches any of the values in the range. If the checker value has the exact case, it will return TRUE.*

*The checker value does not match with any of the values in the range and so shows FALSE.*

** Formula Explanation**

The **TEXT **function takes two sets of text to compare and gives the result as Boolean **True **or **False.**

And the **OR **function takes the logic from where any of the logic needs to be fulfilled to get a result as Boolean **TRUE**. Unless it shows Boolean **False**.

**Method 3 – ISNUMBER with FIND and IF Functions**

Use the nested **ISNUMBER** and **FIND** formula to check if a cell value contains a specific character. The **FIND** function returns the starting position of one text string within another (case-sensitive). For example:

`=ISNUMBER(FIND("T",C7))`

This formula checks if **T** exists in cell **C7** and returns **TRUE** if it does.

**or**

`=ISNUMBER(FIND("j",C7))`

The result shows **TRUE** and **FALSE** respectively.

**Method 4 – Combining IF with ISNUMBER and FIND Functions**

To get a remark instead of Boolean results, combine the **IF** function with the **Method 3** formula. For instance:

`=IF(ISNUMBER(FIND("R",C10)),"MATCHED","Not MATCHED")`

This formula checks if **R** exists in cell **C10**. If it does, it returns **MATCHED**; otherwise, it returns **Not MATCHED**.

**or**

`=IF(ISNUMBER(FIND("a",C10)),"MATCHED","Not MATCHED")`

** Formula Explanation**

The **FIND **function returns the starting position of one text string with another text string. It is case-sensitive by nature. And **ISNUMBER **function returns whether a value is a number and returns **TRUE** or **FALSE**.

The **IF** function checks whether the condition is met and returns **MATCHED**, else it returns **Not MATCHED**.

**Method 5 – Using Nested SUMPRODUCT and EXACT Functions**

We can combine the **SUMPRODUCT** and **EXACT** functions to create a nested formula that matches case-sensitive values. However, keep in mind that the **SUMPRODUCT** function returns numbers, not text. Here’s how it works:

- Modify the dataset to have
**only numbers**in the**ID**column. - Enter the following formula:

`=SUMPRODUCT(--EXACT(C7,C5:C10),B5:B10)`

** Formula Explanation**

- The
**EXACT**function compares two sets of texts and returns a Boolean (**True**or**False**). **EXACT(C7, C5:C10)**produces the array**{FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}**.- The
**SUMPRODUCT**function multiplies corresponding elements of arrays and then sums the results. - The result is
**1102**.

Alternatively, you can enter the formula:

`=SUMPRODUCT(--EXACT(C5,C5:C10),B5:B10)`

to get results for cells **C7** and **C5** in the dataset. This approach works for case-sensitive values.

You can see the result shows for **JANET** and **Janet** correctly.

*Note:**The*

**double minus**(**–**) sign is used to force the conversion of Boolean values to numerical ones.**Method 6 – Applying INDEX and MATCH Functions**

The nested **INDEX **and **MATCH **functions can also handle case-sensitive matches in Excel. This method returns the value if it matches exactly. Let’s check for **JANET** and **Zara** from the dataset:

- Enter the formula:

`=INDEX(C5:C10, MATCH(TRUE, INDEX(EXACT(C7, C5:C10), ), 0))`

** Formula Explanation**

**EXACT(C7, C5:C10)**produces the array**{FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}**.**INDEX(EXACT(C7, C5:C10), )**gives the same array.**MATCH(TRUE, INDEX(EXACT(C7, C5:C10), ), 0)**returns 3.**INDEX(C5:C10, 3)**equals “**JANET**.”

Similarly, enter the formula:

`=INDEX(C5:C10, MATCH(TRUE, INDEX(EXACT(C8, C5:C10), ), 0))`

to find the exact match for both values.

**Method 7 – Utilizing the LOOKUP Function**

The **LOOKUP** function can also be used to match values, but it doesn’t handle case-sensitive issues directly. However, you can nest **LOOKUP** with **EXACT** and **IF** functions to achieve case-sensitive matches. There are two main types of **LOOKUP** functions: **VLOOKUP** and **HLOOKUP**.

**7.1. Using VLOOKUP Function**

Let’s consider the value **ZARA** and use the **VLOOKUP** function:

- Formula:

`=IF(EXACT("ZARA",VLOOKUP("ZARA",C5:C10,1)) = TRUE, "Found", "Not Found")`

** Formula Explanation**

The **VLOOKUP **function looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify. By default, the table must be in ascending order.

**VLOOKUP(“ZARA”, C5:C10, 1)**returns “**ZARA**.”**EXACT(“ZARA”, VLOOKUP(“ZARA”, C5:C10, 1))**evaluates to**TRUE**.- Result: “
**Found**.”

If you change the case of the value, the result will be “**Not Found**.” The result is shown below.

**7.2. Using HLOOKUP Function**

Similarly, the **HLOOKUP** function can be used:

- Formula (for
**Janet**):

`=IF(EXACT("Janet",HLOOKUP("Janet",C5:C10,1)) = TRUE, "Found", "Not Found")`

The result works perfectly. If you change the case, the result will be the opposite.

**Download Practice Workbook**

You can download the practice workbook from here:

**<< Go Back to | Excel Match | Learn Excel**