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

In this step-by-step tutorial, learn to use the MATCH function in Excel to find and match multiple values from two sheets. Once we compare two lists in Excel, we use special conditional formatting formulas to highlight matches. In this video we have a small list of employees in Excel and we want to search for those employees in a larger employee database and when we find a matching employee, we want to highlight the entire row.
The MATCH function will lookup a value from an array on a different sheet and return the number of the row on that sheet where it finds a match. We then wrap the MATCH formula inside ISNUMBER to return either TRUE or FALSE. From there, we can apply a conditional formatting formula to search for "TRUE" and highlight the entire row by using Search and then concatenating the rows you want to highlight. The conditional formatting formula we use in this video is:
=SEARCH("TRUE",$A2&$B2&$C2&$D2&$E2&$F2&$G2)
Next we look at how to use filters to manually highlight the spreadsheet as a way to search and highlight in Excel.
📺 Watch Next - Match Names in 2 Sheets with VLOOKUP: • Match Names on Two Exc...
__________
TIMESTAMPS ⏰
00:00 Find Matching Employees in Excel
00:20 Match Names from Two Sheets
01:00 How to Match Values in Excel
03:15 MATCH Overview
04:00 ISNUMBER Overview
05:00 Conditional Formatting to Highlight Row
08:50 Filter and Highlight Matches in Excel
__________
CONNECT WITH ME 📎
Visit my website: www.sharonsmithhr.com for more information, tools and resources.
LinkedIn: / sharonsmithhr
Twitter: / sharonsmithhr
__________
GEAR
🎙 Blue Yeti USB Microphone: amzn.to/2W4SbzV (Great for recording professional sounding audio for your videos!)
🖱 Silent Mouse: amzn.to/3pxpc25 (This is a really cool mouse!)
🎥 Screen Recording Software: techsmith.z6rjha.net/NZG5b
📗 Green Screen: amzn.to/2DnHsY2
📸 Camera: amzn.to/39KvpQA
🔌 Live Stream Tool: amzn.to/2VFJyID (Turns your DSLR into a top notch webcam)
RESOURCES
✏️ JotForm: www.jotform.com/pricing/?utm_...
📑 Wondershare PDFelement (PDF Editor): bit.ly/31QEkA8
💻 PresenterMedia: shareasale.com/r.cfm?b=417324...
Some links included here are affiliate links. If you click on these links and make a purchase, I may earn a small commission at no additional cost to you. Thanks for supporting this channel!
__________
TEMPLATES
Check out my helpful list of templates available for purchase: www.etsy.com/listing/87584666...
Thank you for supporting my channel!
(Scroll down for a link to my free resources)
__________
NOTE: If you found this content helpful, please consider donating to my channel from this PayPal link: www.paypal.com/cgi-bin/webscr.... Your donation, no matter what amount, is greatly appreciated and goes towards producing content that enhances your productivity and elevates your skills. Thanks for supporting this channel!
__________
FREE DOWNLOADS
✦ Visit mailchi.mp/6a0859ea0844/sharo... to sign-up for my e-mail list and get FREE downloads of super helpful spreadsheet formulas, dashboards and Org Chart templates for HR professionals.
__________
KEYWORDS: #msexcel, @SharonSmith
Find, Match, and Highlight Names from Two Sheets in Excel, Find Matching Values in Two Worksheets, Compare Two Lists in Excel and Highlight Matches, How to search and highlight in Excel, Highlight search results using conditional formatting, Use conditional formatting to highlight rows, Highlight entire row in Excel with Conditional Formatting, Find matching values in Excel
__________
PLAYLISTS:
➤ Jotform Tutorials: • Jotform Tutorials
➤Creating Fillable Forms: • How To Create A Fillab...
➤PowerPoint Tutorials for HR: • PowerPoint Tutorials
➤ Excel Tutorials for HR: • Microsoft Excel Tutorials
➤ Excel Quickies (Around 2 Minutes): • Excel Tips & Tricks
➤ Word Tutorials for HR: • Microsoft Word Tutorials
➤ Welcome | About Me: • Playlist

[@GregATKyruus]

This saved my sanity. Extremely clear directions. Thanks!

[@SharonSmith]

Glad to hear it helped you out! Thanks so much for watching!

[@davinderkandola327]

You are brilliant Sharon! Easy to understand and exactly what i need! Lifesaver!

[@SharonSmith]

So glad you found it helpful!! Thanks for the nice feedback. Thanks for watching!

[@ChuckQuicktossa]

Tina Fey you rock! SNL, and now Excel help online. Honestly though this really helped me thank you!

[@SharonSmith]

Haha! I get that all the time 😂 I'm glad you found it helpful! Thanks for watching!

[@stooeys]

I’d spent the past 4 hours trying to figure this out! Thank you thank you thank you!!! No other vids could crack it!!! Elated!!!!

[@SharonSmith]

I'm so glad you found my video and that it helped you out! Thanks for watching! Take care!

[@CourtneyConnor-xj4py]

This was so helpful and saved me 2 hours of manual work. Thank you

[@SharonSmith]

I’m so glad to hear you saved valuable time! Thanks for watching!

[@1000000trs]

Thanks *Sharon* That was great. Is possible to pull through data from the row on the small list, where it has a match in the big list, and display that data, perhaps where you currently have true/false, or alternitively in a column created next to it for the purpose of displaying "the pulled-though" data ? Thanks again.

[@ingridtrovao3652]

I love the way you explain and show your screen. Thank you for taking the time to make these videos!!!!

[@SharonSmith]

You are very welcome. I’m glad you find my videos helpful! Thanks for watching!

[@KN-jk4cy]

Thank you so very much, Sharon; it's so clear and easy to follow, I Love It!!

[@108ramsita]

You are my hero. THANK YOU for this video.

[@SharonSmith]

Aww - thanks! Glad you found it helpful! Thanks for watching!

[@PerFancyNancy]

This was extremely helpful!! Thank you!

[@SharonSmith]

Glad you found it helpful! Thanks for watching!

[@wasimmuhammad4089]

Very informative and easy step by step video. Thanks

[@SharonSmith]

You are verry welcome, Wasim! Thanks so much for watching my videos!

[@GujjuPocket]

Your video very useful for me ma'am... Thank you Love from India

[@SharonSmith]

Thank you for watching! So glad you like my videos!

[@N.TaraQueen]

Wow this just saved me during a data analysis project! OMG. I had watched a couple of other videos but unfortunately couldn't understand them, so yours was perfect. Thank you.

[@SharonSmith]

So glad to hear my video helped you out! Good luck with your data analysis project! Thanks for watching my videos!

[@ecast1500]

thanks just followed most of your steps saved hours of time

[@SharonSmith]

Awesome! Great to hear. Glad you found it helpful. Thanks for watching!

[@garrardmcclendon1]

Life Saver. Thank you.🤩

[@SharonSmith]

Glad you found it helpful! Thanks for watching!

[@Crazybef]

This just saved me 4 days of work

[@SharonSmith]

That is awesome to hear! Glad it was helpful. Thanks for watching!

[@Dwavard]

Fantastic explainer - i was driving myself mad trying other techniques - thank you for posting this :-)

[@SharonSmith]

So glad you found it helpful! Thanks for watching!

[@GunShot109]

very helpful, Thanks!

[@1drewcarpenter]

Amazing, thank you. I'll be watching more of your videos in the future.

[@SharonSmith]

I'm so glad you found it helpful! Glad you found my channel. Thanks for watching!

[@omarbrjs]

THANK YOU FOR THIS!!!

[@SharonSmith]

Glad you found it helpful! Thanks for watching!

[@cognoscenti_ab]

This is very useful! Thank you very much. Tell me please, is there a way I can export my highlighted (matched rows) to a third spreadsheet? Many thanks in advance.

[@tonyjohn7572]

Psych and Chuck?!....love it! also this was very helpful. Thank you.

[@SharonSmith]

Lol! Yes, two of my favorite shows 😉! Glad you found it helpful! Thanks for watching!

[@AshleeBarrera]

Super helpful, thank you!!

[@SharonSmith]

Glad you found it helpful! Thanks for watching!

[@itsdaj]

Great video. I would like it normally, but this like for for the shoutout to Pierre Despereaux.

[@SharonSmith]

😂 Thank you for the like!!! He's one of my fav TV characters!

[@user-es1or9eg7x]

This video was exactly what I need I watched multiple others and they got me close but I couldn't highlight them first video to actually fully help me.

[@SharonSmith]

Glad to hear this video helped you! Thanks for watching!

[@JamesHonk]

Thank you! Great help

[@SharonSmith]

So glad to help! Thanks for watching!

[@athyscollection]

Thanks this is exactly what I needed.

[@SharonSmith]

So glad to help! Thanks for watching!

[@leokylegonzalesrg1538]

So helpful thank you😊

[@SharonSmith]

Glad to help! Thanks for watching!

[@lee-daniels]

Thank you!

[@SharonSmith]

You are very welcome! Thanks for watching!

[@KM-ox5bh]

Thank you so helpful

[@SharonSmith]

Glad to help! Thanks for watching!

[@moon_knight8578]

well that saved me a load of time. cheers

[@SharonSmith]

Glad to help! Thanks for watching!

[@nabilshaikh2525]

Thank you so much

[@SharonSmith]

Glad to help! Thanks for watching!

[@no1djkamilo]

Great tutorial. I normally use Countif instead of match. For the conditional formatting instead of entering "....$A2&$B2..." i would enter "....$A2:$G2" so that the whole range is selected. But great content!

[@SharonSmith]

Thanks for sharing these tips! Glad you found my videos helpful. Thanks for watching!

[@excellenceexcel]

totally one day wasted, but finally I found your video. Thank you.

[@SharonSmith]

So glad you found my videos! Thanks for watching! Glad it helped you out!

[@nielsvanderschans547]

Thank you thank you thank you just wha I need and very clear! cannot thank you enough ;-)

[@SharonSmith]

I’m so glad you found it helpful! Thanks for watching!

[@vanuakapgb]

The legend, Sharon Smith everyone.

[@SharonSmith]

Thank you! Thanks for watching and subscribing to my Channel! 😊

[@user-sn2hy1qf2t]

Hi, that's really helpful. what is the screen recording software please? . Watching from Germany

[@ruthhay3220]

This tutorial I'm sure can assist me in a project I'm looking at. Can I send an Excel workbook to you and describe what I'd like to achieve from the numbers in the workbook. You don't know it but you may well be solving an issue in the Rail and Maritime Industry..

[@gcomeaux1]

Hi Sharon! I have a master list of data consisting alpha numeric values in Column A in a specific order, In column B some of the values are the same but in a different order. How do I get the matches to be side by side?

[@amitabhasenapati5695]

If you kindly make a video on sitting arrangement in excel, it will help me

[@kpratola]

Is there a way highlight only the rows that have multiple matching values from another workbook or worksheet?

[@victorolivares5079]

Does this work only for unique number values? What if I have a bunch of text in 2 columns? (Verbatims in this case)

[@fabiolacruz5854]

The formula is not working I can't highlight the entire raw just the cell. Someone knows why???😅😅😅

[@nerdymalay97]

Same here Edit: I used the VLOOKUP function and worked out

[@user-hr1sk6sk7o]

Ive created a form in a table in word mac and im trying to lock the text so the form user cant edit it, Eg ive put in "client name" and what the user to insert name. But when i lock the text in developer it locks the whole document not allowing me to type the name in text box. Also the developer is only giving me limited legacy tools.can you help me?

[@wandering_650]

I use when the data is very huge and if you want to find mathicng values and also identify which cell it is matching use this formula: =IFERROR("B is Present in A" & MATCH(B2, A:A, 0), "B is Absent in A")

[@user-ud3mp6mu5c]

How did you get the split window thing? My Excel doesn't do that when you click the minize button

[@SharonSmith]

That is a feature in Windows 11. You should see that option when you hover over the minimize button if you are on Windows 11. Hope this helps! Thanks for watching!

[@KIRANMYCAR]

Hi Sharon, have a query could you pl help? I have a list of employees like first name, middle name and last name in one file and in another file the names are in some different pattern. Can i find the most close match names?

[@sandipdas4765]

madam If i want to merge two large excel sheet into employee.xls, vlookup with other xls having some data, my concern is that how to merge two large scale of excel

[@therealistthereis3852]

This was helpful but now I’m trying to figure out how to match the data from One column to the next couple of columns over and have it highlight the exact match

[@kellyrussell4515]

Trying to pull a value from a column on one worksheet that contains two different plan types (for employee benefits) to another worksheet. Example: Column Header = Plan, Text values in that column = Medical or Dental. Wanting the formula to pull in one value if it's Medical and another value if it's Dental to the other worksheet.

[@HussamQazi]

Hi. I a number that I need to find in a column. which is clubbed amount of 2 numbers or more in another table. How I can find that numbers? Like I want to find what numbers are adding up to make $3000. is there any way?

[@imranfirozabadi]

Hi.. this is the solution i have been looking for. However, when I am selecting the column it not selecting it. What should i do?

[@average_joe3325]

It didnt work for me when trying to writte it down but if I used the function button it was all good, thanks!

[@SharonSmith]

Glad you got it working! Thanks for watching!

[@pavanh.k5280]

I have 2 sheets, tho sheets data's are same but column are interchange (ab to ba) how can I find entire row at single time.....?

[@roset3887]

👍

[@SharonSmith]

Thanks for watching!

[@meniporat3527]

Instead of this lengthy Conditional Formatting rule: =SEARCH("TRUE", $A2&$B2&$C2&$D2&$E2&$F2&$G2) a better option is this: =FIND("TRUE",$B2:$G2)

[@SharonSmith]

Thanks for sharing this helpful tip!

[@pmeghnathi]

How do vlookup formula in excel A1=P b1=D C1=as D1=cd A2=as b2=cd Result E1 CELL IN TRUE C1 AND D1 cell same value match in column A & B

[@SharonSmith]

It seems like you want to use the VLOOKUP formula in Excel to compare the values in cells C1 and D1, and if they match a pair of values in columns A and B, you want to display the corresponding result in E1. Here's how you can do that: Assuming your data is in columns A, B, C, and D, and you want to check if the value in C1 matches a value in column A and the value in D1 matches a value in column B, and then display the corresponding result from column E in cell E1, you can use the following formula in cell E1: =IF(AND(C1"", D1""), IFERROR(VLOOKUP(C1&D1, A:B, 2, FALSE), ""), "") It seems like you want to use the VLOOKUP formula in Excel to compare the values in cells C1 and D1, and if they match a pair of values in columns A and B, you want to display the corresponding result in E1. Here's how you can do that: Assuming your data is in columns A, B, C, and D, and you want to check if the value in C1 matches a value in column A and the value in D1 matches a value in column B, and then display the corresponding result from column E in cell E1, you can use the following formula in cell E1: excel Copy code =IF(AND(C1"", D1""), IFERROR(VLOOKUP(C1&D1, A:B, 2, FALSE), ""), "") Let's break down the formula: AND(C1"", D1""): This part checks if both cells C1 and D1 are not empty. IFERROR(VLOOKUP(C1&D1, A:B, 2, FALSE), ""): If both C1 and D1 are not empty, this part combines the values in C1 and D1 using &, creating a lookup value. It then performs a VLOOKUP in columns A and B using the combined value as the lookup value. The 2 specifies that we want to retrieve the value from the second column (B) of the range A:B. The FALSE argument ensures an exact match. If there's an error (meaning no match was found), it returns an empty string (""). The outermost IF() checks if both C1 and D1 are not empty. If they are empty, it returns an empty string. So, if the values in C1 and D1 match a pair of values in columns A and B, the corresponding value from column E will be displayed in cell E1. If there's no match or if C1 or D1 is empty, cell E1 will be empty. You can drag the formula down in column E to apply it to other rows if needed. Just remember that this formula is case-sensitive, so make sure the values in columns A, B, C, and D match exactly. Hope this helps! Thanks for watching!

[@warnerren1081]

well, I thought this was going to be easier...