Find Multiple Matching Values in Excel and Highlight - Match & Highlight Names from Two Sheets

In this video we're going to take a short list  of employees and we're going to compare it to   a larger employee database and we want to find  that list of employees in that larger employee   database and whenever we find a matching employee  we want to highlight the entire row we're going   to do that using the MATCH formula in Excel  as well as a special formula for conditional   formatting. Let's take a look. Here we have our list of employees that we' re tracking and maybe this is a list of employees who have already completed their timesheet for the week. So we   want to compare this to our main employee database  list and then highlight these on the main employee  database. so I'm just going to go and use the  side-by-side view here just so I can show you   here we have the short list and then over here we  have our long list of our employee database that   we want to find all of these employees on this  main list and highlight them so we're going to   do that using the MATCH formula so let me go ahead  and expand this so we can see it and I'm going to   insert a column here by clicking on column B and  right click and select insert and this is going   to be a column where we're going to use our MATCH  formula so I'm just going to call this find match   and we'll make it a different  color so we know this is where   we put our formula and all we're  going to do is type equals MATCH open parentheses and now we're going to look  up value I know that on both of these sheets   I have employee ID number that I can use as  a lookup value if you don't have an employee   ID number if you have another unique value that's  on both sheets that you can use to compare like a   work email you can use that to look up on but I'm  going to select the cell that has the employee ID   as my lookup value and then I hit comma and now  I want to select the array so this is going to   be the list of employees that I'm tracking that I  want to find so I'm going to go over to that list and I'm going to select the whole column you can  select the whole column or just the range but I'm   going to go ahead and select the column and so  over here that gives us automatically Excel has   put the dollar signs around that column and that  anchors or fixes that range so it won't shift when   we drag the formula down on the other side so  we're going to leave that like it is and then   we're going to come over here and we're going to  type a comma and then we want an exact match so   we're going to type 0 for an exact match and then  close our parentheses and hit enter and notice   over here on our main database we have a #NA and so that means that this employee record was not found to be a match on our other list  so all we have to do now is copy this formula   down so I'm going to bring my cursor over here  to the bottom right corner until it turns into   crosshairs and double click and that will copy the  MATCH formula all the way down on the spreadsheet.   So notice that if it does not find a match it'll  show a #NA if it finds a match it's going   to display a number and what this number is is  actually the row number of where it found that   employees record on the other sheet so for example  we've got down here Pierre Despereaux number five   if we go and look at that other sheet we see  that Pierre Despereaux is on the fifth row of   that spreadsheet so that is what the MATCH formula  is returning it's returning a number the number of   the row where it found the match so now what  we want to do is I want to turn this instead   of having it return either an NA or a number I  want it to return true or false so what I'm going   to do is I'm going to come up to the formula and  right after the equals I'm going to type ISNUMBER and I'm going to open a parenthesis and then  put another closed parentheses at the end and   this wraps the MATCH formula inside the is  number formula and when I hit enter you're   going to see that it will say false and so  now if I select this and copy it down double   click to copy down now it'll show true or false  depending on if it found the match. So now there's   a couple ways that we can highlight the values  that are true we can always select the column   and on the Home tab come over to Conditional  Formatting we can select the Highlight Cells   Rule and we can say Text That Contains and then we  want to type in here True so anything that's True   and then we can select you know maybe a yellow  fill and click OK and it will highlight any True   Value but if we want to highlight the entire row  there is a different Conditional Formatting Rule   that you can apply. So I'm going to go ahead and  let's go ahead and clear that out I'm going to   go back to Conditional Formatting say Clear Rules  From the Entire Sheet and so now what I want to   do is I want to actually select just below the  header I want to select all of the data in here   so I can select one row across from A through  G and then hit Ctrl Shift Down and this will   select all of the data that's in the sheet that I  want to apply this Conditional Formatting Rule to   and then I can come up to the Home tab go to  Conditional Formatting and click on New Rule   from here you're going to say use a formula  to determine which cells to format and then   in here is where we're going to type this Rule and  what we're going to say is equals and then SEARCH   and then we're going to open a parenthesis  and we're going to put in quotes TRUE because   we want to find it's searching for the word  TRUE and if it finds it in that row we want   it to be highlighted so we're searching that  so we then we're going to put comma and then   we are going to concatenate or list out the  rows that we want to apply this highlighted   format to so to do that we're going to say  dollar sign A 2 and then we're going to put   the Ampersand and then do that the same for  all of the columns that we want to highlight   so the next one will be B2 so we're going to  say dollar sign B2 Ampersand dollar sign C2 ampersand dollar sign D2 Ampersand dollar sign E2 Ampersand dollar sign F2 Ampersand dollar sign G2 and then we're going  to close the parentheses and now we're going   to come over here to the Format tab we're  going to select that and come over here if   it's on Number come over to the Fill area and  let's just highlight this in bright yellow so   we can see it easily you can select any color  if you want to highlight just the fill color   of that row you can select the color and then  click ok and now we're going to click on OK   and now we can see that anywhere that there is  a True Value the entire row is highlighted in   yellow so that is how you can find a match from  another spreadsheet and then highlight the entire   row of those found employees now one thing I will  recommend or caution with conditional formatting   is notice that conditional formatting formats  this in the background so if I were to come   out here and try to say change the fill color  on this cell it's not going to let me you know   it's it's conditionally formatted so I can't  easily come out and change the formatting on   that I would have to actually select that area  come out to Conditional Formatting and Clear the   Rule either on the selected cell or from the  entire sheet and so a different way of doing   this if you didn't want to use the Conditional  Formatting because of that reason maybe you   want to come back and change the highlighting  or or do different things on your spreadsheet   another thing you could do I'm going to come  down here and copy my database so that I can   show you what to do I'll just say Move a Copy  on the End and then Create a Copy and click OK and so here I'm going to go ahead and remove  select the whole sheet and remove the rules   from this sheet and then this was the color  that I had selected behind the scenes so I'm   going to unfill that color and so now if I just  want to myself highlight these all I have to do   is come up to Data and go to Filter and then  I can filter on Just My True Values click OK   and then I can just select those come back to the  Home tab select my fill color highlight those and   you can always go back to your Data area and Clear  the Formatting and then you have all of your trues   highlighted as well and then if you wanted to  come and make a change to that formatting then   you can come out and do that and you're able to  make a change on your formattings so there are   a couple of different ways that you can highlight  those values that you find if you found this video   helpful be sure and give it a thumbs up to like  it you can subscribe to my channel and click the   Bell to receive a notification every time I post  a new video be sure and visit my website Sharonsmithhr.com. Thank you so much for watching  and I look forward to seeing you next time!

Channel: Sharon Smith

Views: 117,484

Keywords: compare two lists in excel for matches, find matching values in excel, find matching values in two sheets, find multiple matching values in excel, find matches and highlight, highlight rows in excel, conditional formatting in excel, find matches, find matching names in two sheets, excel tips, excel tutorials, compare two lists, compare two sheets in excel for matches, compare two lists in excel

Id: Fv7kGcQaLk0

Length: 10min 14sec (614 seconds)

Published: Mon Jan 16 2023