30 Excel Functions in 30 Days: 01 – EXACT

Icon30DayWelcome to the Contextures 30 Excel Functions in 30 Days (30XL30D) challenge. Thanks for voting for your favourite functions, and we will cover the top 30 Excel functions (based on total votes), from the following categories:

  • Text
  • Information
  • Lookup and Reference

At the end of the challenge, I’ll post a list of all the functions, sorted by vote ranking. However, the top 30 functions will be covered in random order, so be sure you visit here every day, to catch them all.

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).

To kick off the 30XL30D challenge, here’s the first function — EXACT. There are 7 examples for this function, so if you haven’t used EXACT before, you might be surprised by what it can do. If you have tips for this function, or other examples, please share them in the comments.

Function 01: EXACT

The EXACT function can check for an exact match between text strings, including upper and lower case. Formatting does not affect the result. If the text strings are exactly the same, the function result is TRUE; if they’re not exactly the same, the result is FALSE.

Exact00

How Could You Use It?

Besides checking two cells to see if their contents match exactly, you can use the EXACT function to do the following:

  • Use with data validation to block changes to a cell
  • Force upper case entries in a data validation cell
  • Check for an exact match in a list of codes
  • Find an exact match in a lookup table
  • Count exact matches in a list
  • Pinpoint the differences between 2 cells

EXACT Syntax

The EXACT function has the following syntax:

  • EXACT(text1,text2)
    • Text1 is the first text string.
    • Text2 is the second text string.

You can enter the text1 and text2 arguments as cell references or text strings. In Excel 2007, the maximum string length for EXACT is 32767 characters.

EXACT Traps

In Excel 2007 Help, there is the following statement in the Remarks section for the EXACT function.

“You can also use the double equals (==) comparison operator instead of the EXACT function to make exact comparisons. For example, =A1==B1 returns the same value as =EXACT(A1,B1).”

This is incorrect. There is no “double equals” operator in Excel, and this remark has been removed in the Excel 2010 Help.

Example 1: Test a Password

You’ve entered a secret password in a cell in your workbook, and you named that cell “pwd”. Users will enter a password, and you’ll compare their entry to the contents of the “pwd” cell.

  • In the screen shot below, the secret password is in cell C2, which is named “pwd”. This is on a sheet named AdminData, which can be hidden from users.

Exact01

On another sheet, users will enter the password, and you’ll use the EXACT function to test it.

  • On the Ex01 sheet, the user will type a password in cell C3.
  • In cell C4, the equal operator compares the value in C3 to the value in the pwd cell: = C3=pwd
  • In cell C5, the EXACT function compares C3 to the pwd cell, including case: =EXACT(C3,pwd)

If the contents of the two cells are identical, including upper and lower case, cell C5 will show TRUE as the result. Any formatting differences, such as bold font, will be ignored.

Exact02

If there is a difference in the contents – if even one letter is a different case, the result in C5 will be FALSE.

Exact03

Example 2: Allow Changes to a Cell

After the user enters the correct password, you could allow changes to specific cells in the worksheet. For example, a custom data validation formula in cell C5 could control changes to the Daily Rate.

With the following formula in the data validation dialog box, users can enter a value in cell C5 only if the password typed in C3 is an exact match for the secret password in the pwd cell. Also, the value typed in C5 must be higher than zero, and lower than 0.1.

=AND(EXACT(C3,pwd),C5>0,C5<0.1)

Exact04

Example 3: Force Upper Case Entries

You could also use the EXACT function in data validation to ensure that all upper case letters are typed in a cell. For example, a Canadian postal code is a set format, with alternating numbers, and upper case letters, e.g., L9L 9L9.

In cell C2, data validation has been applied, with the formula: =EXACT(C2,UPPER(C2))

Exact05

If any lower case letters are entered, an error alert will appear. This won’t prevent all errors in the postal code, but will ensure that upper case letters are used.

Example 4: Find an Exact Match in a List

Instead of simply comparing one cell to another, you might need to look for an exact match in a list of values. If someone types a product code in a cell, is that exact code in your product list?

In this example, there is a product code list in cells B2:B5, and a customer can order a product, by typing its code in cell E2.

Exact06

The formula in cell F2 uses the EXACT function to check the code typed in cell E2, and see if there’s an exact match in the list of product codes.

NOTE: The formula is array entered, by pressing Ctrl+Shift+Enter

{=OR(EXACT($B$2:$B$5,E2))}

Example 5: Pull a Name for an Exact Match in a List

In a lookup table, the EXACT function can distinguish between AA1 and Aa1, and help return the correct product name for each code. Other functions, like VLOOKUP, would treat those codes as identical, and return the product name for the first code it encounters in the table.

In this example, there is a product code list in cells B2:B5, and a customer can order a product, by typing its code in cell D2.

Exact07

The formula in cell E2 uses 3 functions – INDEX, MATCH and EXACT:

  • The EXACT function checks the code typed in cell D2, and see if there’s an exact match in the list of product codes.
  • The MATCH function returns the table row number of the TRUE result, and bG8943TO is found in the 2nd row of the range.
  • The INDEX function returns the value in the 2nd row of the range A2:A5 – Sam.

NOTE: The formula is array entered, by pressing Ctrl+Shift+Enter

{=INDEX($A$2:$A$5,MATCH(TRUE,EXACT($B$2:$B$5,D2),0))}

Example 6: Count the Exact Matches in a List

In a lookup table, the EXACT function can distinguish between AA1 and Aa1, and help return the correct count for each code. Other functions, like COUNTIF, would treat those codes as identical, and return the count for all variations of the code.

In this example, there is a Item list in cells A2:A11, and a list of unique items in column C.

Exact08

The formula in column D uses 2 functions – SUMPRODUCT and EXACT:

  • The EXACT function checks the item typed in column C, and see if there’s an exact match in the list of items.
  • The SUMPRODUCT function returns the total count, based on the number of TRUE results.

=SUMPRODUCT(–EXACT($A$2:$A$11,C2))

Note: The two minus signs (double unary) in front of the EXACT function convert the TRUE and FALSE values to 1 and 0 values.

Exact09

Example 7: Check Each Character in a Cell

The EXACT function can tell you if two text strings match exactly, but you might want a quick way to see which characters are not identical. In this example, each string has 6 characters, and the numbers 1 to 6 are entered as column headings.

Exact10

The formula in cell E2 uses 3 functions – MID and EXACT:

  • The MID function returns a specific character from column A or column B, based on the number in the formula’s column heading. For example, in the formula shown above, for C2, the first character in each string is tested, because the number in cell C1 is 1.
  • The EXACT function compares the two characters extracted by the MID functions.

=EXACT(MID($A2,C$1,1),MID($B2,C$1,1))

Download the EXACT Function File

To see a demonstration of today’s examples, you can download the EXACT function sample workbook. The file is zipped, and is in Excel 2007 file format. Try to use each function in your own workbooks. Then, for extra brain-sticking power, teach a friend or co-worker how to use each function. When you explain it to someone else, you’ll remember it better.

Watch the EXACT Video

To see the examples in the EXACT function sample workbook, you can watch this Excel video tutorial.

YouTube link: Compare Cells With Excel EXACT Function

_____________

You may also like...

18 Responses

  1. leezhihong says:

    Thank you for your shares!

    “=SUMPRODUCT(–EXACT($A$2:$A$11,C2))” also can be aqual to “{=SUM(–EXACT($A$2:$A$11,C2))}”.

  2. Thanks a lot for the share!

  3. ????? says:

    hi;very thank you

  4. Gregory says:

    The SUM function is sufficient for example 6 and I’m not sure why you decided to use SUMPRODUCT.

    There are some very nice examples in this post. Thanks for taking the time to put this together. It’s helpful.

  5. @Gregory, yes, you could use SUM, if you array-enter the formula. With SUMPRODUCT, the array-enter isn’t required.

  6. Gregory says:

    I guess after looking at example 5, I thought example 6 was also an array. My mistake, and thanks for setting me straight.

  7. There were so many examples for this function, it’s no wonder that you lost track! ;-)

  8. Ulrik says:

    Very informative – thank you for bringing to attention this overlooked function.
    I especially liked the sorting for unique and case sensitive values in example 6. Simple and useful!

    This bodes well for the coming 29 days!

  9. Contextures Blog » 30 Excel Functions in 30 Days: 24 - INDEX says:

    […] EXACT to find name for the password with exact […]

  10. Contextures Blog » 30 Excel Functions in 30 Days: Conclusion says:

    […] EXACT […]

  11. Rizchan says:

    Thanks it’s very useful and improve my knowledge.

  12. Claudenir says:

    Wow! Great examples you taught us! I hope I could use it in my job. I can’t wait to see the others examples.

  13. Vini says:

    =IF(EXACT(C11,”-“),IF(EXACT(D11,”-“),E11,D11),C11)

    How correct is this formula??
    How can this be modified if incorrect??

  14. Dave says:

    Wow! This is great! Thank you for sharing. I’d never considered EXACT could be such useful and versatile function. Glad I checked this site out.
    Thanks, again.

  15. Neil says:

    Thanks for the assist

    Your example above, gives me =INDEX($A$7:$A$22,MATCH(TRUE,EXACT($B$7:$B$22,B5),0)) in my sheet. But it gives me errors stating the #N/A or #Value! and I see no difference between our sheets and I made sure all is general. please assist. TIA. If I follow calculation steps in excel it says that after true,false in italic… or the B$ range is where the error will occur…

    • Eugene says:

      Neil, the problem is you entered a normal excel formula, what need is an array formula. An Array formula has a “{” at the beginning and “}” at the end, see the example above. You can not just type them in. What you need to do is go back to the formula and press F2 to edit the formula and then press Ctrl+Shift+Enter all at the same time. This will add the {}. For more info on array formulas check out Mike Girvin at http://www.excelisfun.com he is the expert on arrays.

      This is the first time I’ve answered a question I hope it help.

Leave a Reply

Your email address will not be published. Required fields are marked *