Compare data with EXACT, FIND, and SEARCH functions

- [Instructor] Excel has a number of different ways to compare data in different locations. On this worksheet, we've got some data in columns a and b. Now, at a glance, for example, a1, b1 look different. So does cell a2 and b2, but they're just a question of upper and lowercase, but in these different examples here, the problems we run into at different times often revolve around the letter i, the letter l, the number zero and capital o, and as we approach this, we could be using, for example, the if function.

I'm gonna format it, prepare it in cell c1. I'll simply double-click. I'm gonna remove that leading space. What are we trying to do here? If these two match up, if they're identical, we wanna see the phrase match. Otherwise, no match. So when we press enter here, some of you might be surprised we have a match, but they certainly look different. The character that follows the number seven over here in cell b1 is a lowercase i. If these match up, that means that the character over in cell a1 following a letter seven is an i also, but it's a capital i.

Now, that might be okay, in other words you might want this to be the case and maybe that is the match, fine. I'm gonna double-click to copy this down the column into the other cells. Let me see what's happening here. We're using the same kind of function, I'll display the formula, in each of these cells here. It's pretty obvious the two Januarys contain the exact same characters, but again, there's that upper and lowercase. And there will be situations where sometimes they want a certain result, sometimes not, so at least we see what's happening in these examples here. We can effectively achieve the same objective by simply writing a formula like this, and I'll take out that leading space, complete the entry.

And we'll simply get true or false here. And we're getting the same kind of answers actually that we're getting in column c, but obviously they're being displayed differently. Now, if we use the function called exact, think of this as meaning exact and it is case sensitive. Remove that leading space, I'll press Ctrl + Enter so the function is complete, but the active cell doesn't move. That's false. Even though that character following the number seven in both cases over in cells a1 and b1 happens to be an i, one's uppercase, one's not.

And we'll see what's happening. None of those match up properly as we use the exact function. Now, a lot of these situations occur when you're using coding schemes, and when you are using codes, if you have anything to say about the creation of the code, I would strongly suggest think out the whole issue whether you want to use zeros and ones and o's and so forth, and some of the examples that we're seeing over here, as we see that result's displayed to the left here, give us some ideas, some thoughts.

Now, one approach might be, what if we use a different font in Excel? We can certainly do that. I'm gonna highlight these and change the font to the native font that we see in Microsoft Word, Times New Roman, some of you are probably familiar with that. Now, that might be better, it might not be, but take a look at these two characters here. Those are very subtle, and when and they're next to each other like we see them down here, we've got some issues. Now, two other functions that come into play here when we're trying to match up these, would be the search function.

We're looking for the letter i over in cell h1, so the form is already prepared here. I'll double-click, remove that leading space, and press enter. Are we gonna find the letter i? We found it, and where do we find it? It's in the second character. Now, the search function is not case-sensitive. We're looking for the letter i no matter what. Uppercase, lowercase, we found it. And let me double-click the lower right-hand corner, copy this down the column.

We don't find it here. We find it here in the second position, and there it's more apparent because we actually see it being displayed that way. The find function, the companion to search, is case-sensitive. Are we going to find the letter i here? The second character is an i. Now, we don't find it. So in working with any kind of data where you are trying to do matching, be sensitive about upper and lowercase. Sometimes they'll use the function called exact when you're trying to do a match, and sometimes when you're trying to locate information using find or search, recognize there is a difference in how the two functions work, and there is no ideal solution in terms of making these codes appear more readable.

You might experiment if you have the time with the different font out here. That one's not gonna work, there are probably lots of these that don't work so well. Old English, so on. But it's worth exploring and recognizing that whenever we're trying to make comparisons, particularly with unusual kinds of coding structures, we may need to rely upon these various functions as we've seen them here, exact, search, and find.