Excel MATCH Function and Conditional Formatting: Step-by-Step Data Comparison and Analysis

Microsoft Excel is a powerful tool for data analysis and management. Two features that can be particularly useful when working with data lists are the MATCH function and Conditional Formatting. The MATCH function helps you locate the position of a specified item in a range, while Conditional Formatting allows you to visually highlight information in your spreadsheet based on certain criteria. When used together, these features can help you compare and analyze data lists efficiently. In this blog, we’ll explore how to use the MATCH function in conjunction with Conditional Formatting to compare data lists and gain insights from your data.

 

Understanding the MATCH Function


The MATCH function in Excel searches for a specified value in a range and returns the relative position of that item. The syntax of the MATCH function is as follows:

“`
MATCH(lookup_value, lookup_array, [match_type])
“`

– `lookup_value`: The value you want to match in `lookup_array`.
– `lookup_array`: The range of cells containing possible matches.
– `[match_type]`: Optional. The number -1, 0, or 1. The default value is 1. Use 0 for an exact match.

Example of MATCH Function

Suppose you have a list of employee names in column A and you want to find the position of a specific employee named “John Doe” in that list.

| A          |
|————|
| Jane Smith |
| John Doe   |
| Emily Ray  |

You would use the MATCH function as follows:

“`
=MATCH(“John Doe”, A1:A3, 0)
“`

This formula would return `2` because “John Doe” is in the second position in the list.

 

Applying Conditional Formatting


Conditional Formatting in Excel allows you to apply formatting to cells based on certain conditions. This can be useful for highlighting differences, outliers, or important trends in your data.

Example of Conditional Formatting

Imagine you have two lists of employee names, and you want to highlight the names in the first list that do not appear in the second list.

| List 1      | List 2      |
|————-|————-|
| Jane Smith  | Emily Ray   |
| John Doe    | Jane Smith  |
| Emily Ray   | Peter Pan   |

You can use Conditional Formatting to highlight “John Doe” in List 1 because it does not appear in List 2.

 

Step-by-Step Solution: Comparing Data Lists with MATCH and Conditional Formatting

 

Step 1: Prepare Your Data

Organize your data into two lists that you want to compare. For this example, we’ll use the employee names lists mentioned above.

Step 2: Use the MATCH Function

Next to the first list, use the MATCH function to search for each name in the second list.

| List 1      | Match Result | List 2      |
|————-|————–|————-|
| Jane Smith  | 2            | Emily Ray   |
| John Doe    | #N/A         | Jane Smith  |
| Emily Ray   | 1            | Peter Pan   |

The formula in the Match Result column for the first cell would be:

“`
=MATCH(A2, C$2:C$4, 0)
“`

Drag the formula down to apply it to the rest of the cells in the Match Result column.

Step 3: Apply Conditional Formatting

Select the cells in the first list, then go to the Home tab, click on Conditional Formatting, and choose “New Rule.”

Choose “Use a formula to determine which cells to format” and enter the following formula:

“`
=ISNA(B2)
“`

This formula checks if the MATCH function returned #N/A, indicating that the name was not found in the second list.

Choose a format (e.g., fill color) and click OK.

Step 4: Analyze the Results

After applying Conditional Formatting, any names in List 1 that do not appear in List 2 will be highlighted. This visual cue makes it easy to spot differences between the two lists.

 

Conclusion


By combining the MATCH function with Conditional Formatting, you can effectively compare and analyze data lists in Excel. This method provides a clear and visual way to identify discrepancies, duplicates, or unique entries between lists. Whether you’re reconciling data or simply trying to find mismatches, these tools can save you time and enhance your data analysis capabilities. Practice with different datasets to become proficient in using MATCH and Conditional Formatting together, and you’ll be well on your way to mastering Excel for data comparison.

 

Live Excel Support Transcript Example:

Below is a live transcript of one of our Excel Support experts helping a customer identify matching records in Excel.

Full Conversation:

Customer: That Chrome was better, but it didn’t give me a chance to like open a new browser. So will this be okay?
Technician: Yeah, that’s fine.
Customer: Okay. So you want like some sort of conditional format I think so it was what is called I get very confused yeah, and like everything I found online was all about like functions and formulas and I didn’t want to do it wrong. It just was really confusing to me. So, okay, do you mind sharing your screen and then you can kind of run me through what your end result is?
Technician: Yeah, let me open. I have a billion files too. So let me make sure I have the right ones open.
Customer: Okay, let me know when you see this.
Technician: Like I will. Have you still like that something to share yet because usually once you hit it you also have to click share, right?
Customer: It’s okay, click it.
Technician: Yeah, you click the green button it should bring up different screens or windows you can choose to share the entire screen. And then from there the bottom button that says share should highlight or something like that.
Customer: You know, it’s not doing anything. I click that button then I click the start share.
Technician: So in this green button here, yeah exactly. Yep that one and then this share screen at the bottom and it didn’t do anything.
Customer: Hmm, that’s strange. It is strange. I agree. Should I try and switch this Chrome? I just don’t know.
Technician: Yeah, you can try to log on through there. The timer still hasn’t started anyway. And then we can go from there.
Customer: Okay. Sorry. Let me just go to Geeker dot CEO.
Technician: Yeah.
Customer: It’s asking me to tell you about my business, but I don’t want to do this right now. There’s no skip button or something there. I’m just gonna put I’m not a business owner.
Technician: Okay. Alright. Okay, so if yeah, I’m trying to see how I how I would rejoin this from. So once you get in there from your dashboard, you should see the job and yeah, they couldn’t join.
Customer: Oh, okay. Alright, I’m gonna sign out of Safari and then join, okay.
Technician: Okay. Alright. Thank you. Yeah, no worries.
Customer: All righty, there it goes, okay. Alright, let me open up the rate. Yeah, that’s one of these ones. Let’s just do this one. And then this guy so I know I can go to view Arrange all and put them side by side. And yeah.
Technician: So, what are you trying to compare and I’m trying to compare the And yeah. So, what are you trying to compare and So I have a bunch of different tables. So basically this one on my right I don’t for showing the one that says FN FH benchmarks allowed medical. These are rates. I guess you can’t see it from above that are in the starts with like I think the 50th. It’s a 95th percentile. And so I want to compare and then these are codes here. I don’t necessarily need anything in the middle, but I want to compare because This that I got from an insurance company has more codes than this one. So I want to eliminate All the codes that aren’t in both which is kind of Hard because you know the ones that are skipped aren’t like necessarily in chronological order, but then I still want to keep you know, like all the numbers or whatever here and then be able to compare and contrast like Like column D because that’s not facility with with each column here I Think so, okay. So the first thing is you want to get rid of duplicate Or not duplicates, but essentially numbers that are not being used in the right side. So in column a of both They both have some of the same codes, but they have different ones as well. So yeah, I wanted to get rid actually I guess Technically would be get rid of all the codes that Are not sorry For this fee schedule on the left I Would like to get rid of all any of the codes that aren’t in this feast or this benchmark file on the right. Yeah for both columns.
Technician: Okay. Do you want me to remote in or do you want me to just guide you?
Customer: Guide me because I’d like to do this myself as well. And if I mess up I will Okay, and ask you to remote in.
Technician: Yeah, no worries. It’s okay. I’m just trying to make sure we do this, right?
Customer: There’s nothing. Okay, so I think that’ll be how we do it. That’s a that means it’ll come up with number. Okay, interesting. So we will say And I’ll just say Used Used Otherwise Used This This Here What? And This And Jeff, it’s faster for you to remote in and I can just like do a return demonstration afterwards. I don’t want to like hamper your process.
Technician: No, no, no, it’s fine. You’re not, it’s just I I kind of go through thinking. Okay, here we go. So, what we’ll do I’m just trying to figure out Here, let me show you mine. If it lets me, it does not. Okay, maybe it’s because I’m full screen right now. Hold on. So, if I jump out of my full screen. Should I minimize my Excel window, son? Oh, no, it’s okay. Here we go. So, no, I was just trying to figure out making sure we’re doing this the right way. So, essentially, we have it from here. Looking it up in this value. And then, as long as it’s there, it’ll be used or not used. So, you can go ahead and kind of copy this format. Essentially. Let’s click into Let me see. Go left. So, hit your left arrow. Left arrow, sorry. Yeah, like go into G1. Okay. Okay, so, from here. You’re going to type in equals if. Equals if. Okay, and then you’re going to do is number. Tab or just keep writing? Oh, parenthesis and then is number. I’m sorry. Parenthesis. Oh, parenthesis, sorry. Okay. Is num. Do I do a No space. No space, okay, is number. And then, you can hit tab. Anytime you see it highlighted at the bottom. You can hit tab and it’ll automatically complete it for you. Oh, okay. So, from here, type in match. Match. Okay. And then, you’re going to hit Is that what I’m supposed to do? Yeah. Okay. You’ll do G1. Okay. Comma. Okay. And then, you’re going to click over on the column N. Over on the right side. Okay. Click on it. Like that? There it goes. Now, let’s see, it’s already locked. Okay, now do A comma. And a zero. Okay, comma here. Yeah. Zero. Okay. Yeah, and then close that out. Okay. And you’re going to do one more. Closing parenthesis again. Okay. And then, a comma. Okay. You’re going to do in quotes, you’re going to type in used. Used? Yeah. And then, another quote. Comma. And then, not used. Also in quotes. Okay. And then, close that out. And that should be it. For that. Press enter. Now, click into Let me see. Go left. So, hit your left arrow. Left arrow, sorry. Yeah, like go into G2. Oh, okay. Let me see, so we’re looking for Okay, let me see so we’re looking for You see, oh no, let me do it on my side because I’m okay myself. Yeah, I can remote you in if you feel like that’s easier. I just want to know how to be able to do this. I haven’t done this stats like 20 years ago. Yeah, no worries. It’s okay. I’m just trying to make sure we do this, right? I There’s nothing. Okay, so I Think that’ll be how we do it. That’s a that means it’ll come up with number. Okay, interesting. So we will say And I’ll just say Used Used Otherwise Used This This Here What? And This And Jeff, it’s faster for you to remote in and I can just like do a return demonstration afterwards. I don’t want to like hamper your process. No, no, no, it’s fine. You’re not, it’s just I I kind of go through thinking. Okay, here we go. So, what we’ll do I’m just trying to figure out Here, let me show you mine. If it lets me, it does not. Okay, maybe it’s because I’m full screen right now. Hold on. So, if I jump out of my full screen. Should I minimize my Excel window, son? Oh, no, it’s okay. Here we go. So, no, I was just trying to figure out making sure we’re doing this the right way. So, essentially, we have it from here. Looking it up in this value. And then, as long as it’s there, it’ll be used or not used. So, you can go ahead and kind of copy this format. Essentially. Let’s click into Let me see. Go left. So, hit your left arrow. Left arrow, sorry. Yeah, like go into G1. Okay. Okay, so, from here. You’re going to type in equals if. Equals if. Okay, and then you’re going to do is number. Tab or just keep writing? Oh, parenthesis and then is number. I’m sorry. Parenthesis. Oh, parenthesis, sorry. Okay. Is num. Do I do a No space. No space, okay, is number. And then, you can hit tab. Anytime you see it highlighted at the bottom. You can hit tab and it’ll automatically complete it for you. Oh, okay. So, from here, type in match. Match. Okay. And then, you’re going to hit Is that what I’m supposed to do? Yeah. Okay. You’ll do G1. Okay. Comma. Okay. And then, you’re going to click over on the column N. Over on the right side. Okay. Click on it. Like that? There it goes. Now, let’s see, it’s already locked. Okay, now do A comma. And a zero. Okay, comma here. Yeah. Zero. Okay. Yeah, and then close that out. Okay. And you’re going to do one more. Closing parenthesis again. Okay. And then, a comma. Okay. You’re going to do in quotes, you’re going to type in used. Used? Yeah. And then, another quote. Comma. And then, not used. Also in quotes. Okay. And then, close that out. And that should be it. For that. Press enter. Now, click into Let me see. Go left. So, hit your left arrow. Left arrow, sorry. Yeah, like go into G2. Oh, okay. Let me see, so we’re looking for Okay, let me see so we’re looking for You see, oh no, let me do it on my side because I’m okay myself. Yeah, I can remote you in if you feel like that’s easier. I just want to know how to be able to do this. I haven’t done this stats like 20 years ago. Yeah, no worries. It’s okay. I’m just trying to make sure we do this, right? I There’s nothing. Okay, so I Think that’ll be how we do it. That’s a that means it’ll come up with number. Okay, interesting. So we will say And I’ll just say Used Used Otherwise Used This This Here What? And This And Jeff, it’s faster for you to remote in and I can just like do a return demonstration afterwards. I don’t want to like hamper your process. No, no, no, it’s fine. You’re not, it’s just I I kind of go through thinking. Okay, here we go. So, what we’ll do I’m just trying to figure out Here, let me show you mine. If it lets me, it does not. Okay, maybe it’s because I’m full screen right now. Hold on. So, if I jump out of my full screen. Should I minimize my Excel window, son? Oh, no, it’s okay. Here we go. So, no, I was just trying to figure out making sure we’re doing this the right way. So, essentially, we have it from here. Looking it up in this value. And then, as long as it’s there, it’ll be used or not used. So, you can go ahead and kind of copy this format. Essentially. Let’s click into Let me see. Go left. So, hit your left arrow. Left arrow, sorry. Yeah, like go into G1. Okay. Okay, so, from here. You’re going to type in equals if. Equals if. Okay, and then you’re going to do is number. Tab or just keep writing? Oh, parenthesis and then is number. I’m sorry. Parenthesis. Oh, parenthesis, sorry. Okay. Is num. Do I do a No space. No space, okay, is number. And then, you can hit tab. Anytime you see it highlighted at the bottom. You can hit tab and it’ll automatically complete it for you. Oh, okay. So, from here, type in match. Match. Okay. And then, you’re going to hit Is that what I’m supposed to do? Yeah. Okay. You’ll do G1. Okay. Comma. Okay. And then, you’re going to click over on the column N. Over on the right side. Okay. Click on it. Like that? There it goes. Now, let’s see, it’s already locked. Okay, now do A comma. And a zero. Okay, comma here. Yeah. Zero. Okay. Yeah, and then close that out. Okay. And you’re going to do one more. Closing parenthesis again. Okay. And then, a comma. Okay. You’re going to do in quotes, you’re going to type in used. Used? Yeah. And then, another quote. Comma. And then, not used. Also in quotes. Okay. And then, close that out. And that should be it. For that. Press enter. Now, click into Let me see. Go left. So, hit your left arrow. Left arrow, sorry. Yeah, like go into G2. Oh, okay. Let me see, so we’re looking for Okay, let me see so we’re looking for You see, oh no, let me do it on my side because I’m okay myself. Yeah, I can remote you in if you feel like that’s easier. I just want to know how to be able to do this. I haven’t done this stats like 20 years ago. Yeah, no worries. It’s okay. I’m just trying to make sure we do this, right? I There’s nothing. Okay, so I Think that’ll be how we do it. That’s a that means it’ll come up with number. Okay, interesting. So we will say And I’ll just say Used Used Otherwise Used This This Here What? And This And Jeff, it’s faster for you to remote in and I can just like do a return demonstration afterwards. I don’t want to like hamper your process. No, no, no, it’s fine. You’re not, it’s just I I kind of go through thinking. Okay, here we go. So, what we’ll do I’m just trying to figure out Here, let me show you mine. If it lets me, it does not. Okay, maybe it’s because I’m full screen right now. Hold on. So, if I jump out of my full screen. Should I minimize my Excel window, son? Oh, no, it’s okay. Here we go. So, no, I was just trying to figure out making sure we’re doing this the right way. So, essentially, we have it from here. Looking it up in this value. And then, as long as it’s there, it’ll be used or not used. So, you can go ahead and kind of copy this format. Essentially. Let’s click into Let me see. Go left. So, hit your left arrow. Left arrow, sorry. Yeah, like go into G1. Okay. Okay, so, from here. You’re going to type in equals if. Equals if. Okay, and then you’re going to do is number. Tab or just keep writing? Oh, parenthesis and then is number. I’m sorry. Parenthesis. Oh, parenthesis, sorry. Okay. Is num. Do I do a No space. No space, okay, is number. And then, you can hit tab. Anytime you see it highlighted at the bottom. You can hit tab and it’ll automatically complete it for you. Oh, okay. So, from here, type in match. Match. Okay. And then, you’re going to hit Is that what I’m supposed to do? Yeah. Okay. You’ll do G1. Okay. Comma. Okay. And then, you’re going to click over on the column N. Over on the right side. Okay. Click on it. Like that? There it goes. Now, let’s see, it’s already locked. Okay, now do A comma. And a zero. Okay, comma here. Yeah. Zero. Okay. Yeah, and then close that out. Okay. And you’re going to do one more. Closing parenthesis again. Okay. And then, a comma. Okay. You’re going to do in quotes, you’re going to type in used. Used? Yeah. And then, another quote. Comma. And then, not used. Also in quotes. Okay. And then, close that out. And that should be it. For that. Press enter. Now, click into Let me see. Go left. So, hit your left arrow. Left arrow, sorry. Yeah, like go into G2. Oh, okay. Let me see, so we’re looking for Okay, let me see so we’re looking for You see, oh no, let me do it on my side because I’m okay myself. Yeah, I can remote you in if you feel like that’s easier. I just want to know how to be able to do this. I haven’t done this stats like 20 years ago. Yeah, no worries. It’s okay. I’m just trying to make sure we do this, right? I There’s nothing. Okay, so I Think that’ll be how we do it. That’s a that means it’ll come up with number. Okay, interesting. So we will say And I’ll just say Used Used Otherwise Used This This Here What? And This And Jeff, it’s faster for you to remote in and I can just like do a return demonstration afterwards. I don’t want to like hamper your process