Find Multiple Matching Values in Excel and Highlight - Match & Highlight Names from Two Sheets
Vložit
- čas přidán 7. 08. 2024
- 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
A year later, these instructions and video editing are still high caliber! You made my month! A big thank you
So glad you found it useful! Thanks for watching my videos!
This saved my sanity. Extremely clear directions. Thanks!
Glad to hear it helped you out! Thanks so much for watching!
Tina Fey you rock! SNL, and now Excel help online. Honestly though this really helped me thank you!
Haha! I get that all the time 😂 I'm glad you found it helpful! Thanks for watching!
You are brilliant Sharon! Easy to understand and exactly what i need! Lifesaver!
So glad you found it helpful!! Thanks for the nice feedback. Thanks for watching!
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!!!!
I'm so glad you found my video and that it helped you out! Thanks for watching! Take care!
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.
So glad to hear my video helped you out! Good luck with your data analysis project! Thanks for watching my videos!
I love the way you explain and show your screen. Thank you for taking the time to make these videos!!!!
You are very welcome. I’m glad you find my videos helpful! Thanks for watching!
Thank you so very much, Sharon; it's so clear and easy to follow, I Love It!!
This was so helpful and saved me 2 hours of manual work. Thank you
I’m so glad to hear you saved valuable time! Thanks for watching!
Fantastic explainer - i was driving myself mad trying other techniques - thank you for posting this :-)
So glad you found it helpful! Thanks for watching!
You are my hero. THANK YOU for this video.
Aww - thanks! Glad you found it helpful! Thanks for watching!
This was extremely helpful!! Thank you!
Glad you found it helpful! Thanks for watching!
Very helpful Sharon. Thank you very much.
So glad you found it helpful! Thanks for watching!
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.
Glad to hear this video helped you! Thanks for watching!
Your video very useful for me ma'am...
Thank you
Love from India
Thank you for watching! So glad you like my videos!
Psych and Chuck?!....love it!
also this was very helpful. Thank you.
Lol! Yes, two of my favorite shows 😉! Glad you found it helpful! Thanks for watching!
Amazing, thank you. I'll be watching more of your videos in the future.
I'm so glad you found it helpful! Glad you found my channel. Thanks for watching!
thanks just followed most of your steps saved hours of time
Awesome! Great to hear. Glad you found it helpful. Thanks for watching!
Very informative and easy step by step video.
Thanks
You are verry welcome, Wasim! Thanks so much for watching my videos!
Life Saver. Thank you.🤩
Glad you found it helpful! Thanks for watching!
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.
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!
Thanks for sharing these tips! Glad you found my videos helpful. Thanks for watching!
Great video. I would like it normally, but this like for for the shoutout to Pierre Despereaux.
😂 Thank you for the like!!! He's one of my fav TV characters!
This just saved me 4 days of work
That is awesome to hear! Glad it was helpful. Thanks for watching!
THANK YOU FOR THIS!!!
Glad you found it helpful! Thanks for watching!
very helpful,
Thanks!
Super helpful, thank you!!
Glad you found it helpful! Thanks for watching!
Thanks this is exactly what I needed.
So glad to help! Thanks for watching!
well that saved me a load of time. cheers
Glad to help! Thanks for watching!
Thank you! Great help
So glad to help! Thanks for watching!
Thank you thank you thank you just wha I need and very clear! cannot thank you enough ;-)
I’m so glad you found it helpful! Thanks for watching!
totally one day wasted, but finally I found your video. Thank you.
So glad you found my videos! Thanks for watching! Glad it helped you out!
Thank you!
You are very welcome! Thanks for watching!
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.
So helpful thank you😊
Glad to help! Thanks for watching!
The legend, Sharon Smith everyone.
Thank you! Thanks for watching and subscribing to my Channel! 😊
Thank you so helpful
Glad to help! Thanks for watching!
Not sure if you monitor this video for comments but I can’t find any instructional videos for comparing two columns to find a matching name IN A TEXT STRING.
Ie one cell may be Mr. John smith in one column and John Smith , Mr. in the second column.
I want to be able to match the name Smith. Thank you. I do find your explanation easy to understand so I’m hoping you have a simple solution
Thank you so much
Glad to help! Thanks for watching!
If you kindly make a video on sitting arrangement in excel, it will help me
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")
Thanks
Thanks for watching!
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..
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?
Does this work only for unique number values? What if I have a bunch of text in 2 columns? (Verbatims in this case)
Hi, that's really helpful. what is the screen recording software please? . Watching from Germany
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?
How did you get the split window thing? My Excel doesn't do that when you click the minize button
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!
Is there a way highlight only the rows that have multiple matching values from another workbook or worksheet?
The formula is not working I can't highlight the entire raw just the cell. Someone knows why???😅😅😅
Same here
Edit: I used the VLOOKUP function and worked out
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.
It didnt work for me when trying to writte it down but if I used the function button it was all good, thanks!
Glad you got it working! Thanks for watching!
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?
Is there any other way to highlith all row, because my sheet goes upto Z column.
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?
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
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?
How to I lookup value, if my lookup array is scattered all from A1 to Z50 (multiple rows and columns)?
I have unique ID in one row in one sheet, and same or different IDs scattered from A1 to Z50 in sheet 2
i have to search and lookup if data matchs from sheet1 to sheet 2,
how do i lookup in multiple rows and columns??
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
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.....?
👍
Thanks for watching!
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)
Thanks for sharing this helpful tip!
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
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!
well, I thought this was going to be easier...