Match Names on Two Excel Sheets with VLOOKUP - Create Unique ID in Excel
HTML-код
- Опубликовано: 3 авг 2024
- How to match and compare names from two different Excel spreadsheets using VLOOKUP // In this example, we have a master spreadsheet of employee names and then a tracking spreadsheet with the employee names who have completed a task and we want to match the names on the master spreadsheet so we can see who still needs to complete the task. We use VLOOKUP and column filters to accomplish this. Then we look at how to create a column of unique identifiers if one does not already exist on both sheets, so we can use VLOOKUP. To do this, we use Text to Columns and also the Concat formula.
🚀 Take your form-building skills to the next level with my Course on Creating Fillable Forms in Microsoft Word:
• Creating Fillable Form...
▶ Watch Next - VLOOKUP Tutorial: • VLOOKUP Excel Tutorial...
__________
RESOURCES & 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)
✏️ 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.
__________
CONNECT WITH ME:
Visit my website: www.sharonsmithhr.com for more information, tools and resources.
LinkedIn: / sharonsmithhr
Twitter: / sharonsmithhr
__________
KEYWORDS: #MSExcel, how to match names in Excel, how to compare names from two sheets in Excel, VLOOKUP, CONCAT, Text to Columns, how to compare list of names from two sheets, Excel formulas for HR, Excel tips and tricks, Sharon Smith HR, @SharonSmithHR
__________
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
Want to take your form-building skills to the next level? 🚀 Checkout my RUclips Course on "Creating Fillable Forms in Microsoft Word: A Step-by-Step Guide," ruclips.net/p/PLb80efKTbWuNzAyHVMYrPOS7itBm5gWVs where your frequently asked questions on forms are answered, like how to insert a Submit form button that works with any email client and how to export completed form data to Excel quickly and easily.
💡 The Course includes 15 video lessons, a handy PDF workbook with all lesson summaries and VB code tidbits, and two versions of the Word form template we build together (a behavior-based interview questionnaire form).
You can still always access all the free content on my Channel (over 100+ video tutorials). Please note that the RUclips Courses feature is in Beta and currently limited to a subset of Creators. Courses are only available in the United States, South Korea, and India. Viewers must use a computer or Android device to buy a Course. We hope RUclips Courses will roll out to more Creators, devices, and regions in the future!
Thank you for taking the time to checkout my Course! You may see some of your other favorite RUclips Creators who have also launched Courses by going to their Channel page and clicking on the "Playlist" tab. Enjoy!
Thank you. I have watched probably 15 of these videos and yours is the first one that finally made sense in my brain.
That is a great compliment, Kathy! Thank you! I'm so glad my video made sense to you. That is what I aim for. Thanks for watching!
Thank you, Sharon! You solved a major situation for us with a database export from two tables. A HUGE THANK YOU from over here!
That is great to hear! I'm glad my video helped you all out. Thanks for taking time to leave a nice comment, I really appreciate it! Thanks for watching!
This video is the easiest explanation I have found for the VLOOKUP function. I work in agriculture and used this to match nearly 2,000 blocks with appropriate yield estimates. I update this regularly throughout growing seasons and this has saved me countless hours. Thank you so much for sharing, you rock!
That is awesome, Roy! So glad to help. Thanks for letting me know and for watching my videos. Take care!
I reference this video all the time. Thank you for the clear instructions.
No BS, straight to point. Clear and concise
Glad you found it helpful! Thanks for watching!
Thanks you have helped me as I am a recent graduate working payroll.
Congratulations on your new position in payroll! So glad to hear my video helped you. Thanks for watching!
Wow thank you for making a video that wasn't an excel 101 compare exact list conditional formatting video. This was exactly what I needed
Glad you found it helpful, Scottie! Thanks for watching and talking time to leave a nice comment. I appreciate it!
Thank you, the video was beneficial and the explanation was straight to the point.
Thank you so much. I have a master data file for students. and I use computerised test results that only shows IDs & Score. This video helped me to easily copy students scores to the master file easily!
Thank you Sharon!It was helpful for me.I understood you easly
Glad to help! Thanks for watching!
This is Outstanding Sharon!!! I needed to cross-reference 2 spreadsheets. This saved me hours of frustration.
So glad to hear you found it helpful and it is saving you time! Thanks for watching!
I forgot how to do it, and had to come back again to watch your video 😂 🙏
Awesome - thanks for watching it again! Glad you found it helpful!!
Thank you!!!
Glad to help! Thanks for watching!
This is super super helpful. Thank you so much! I have 5000 records to check and match
Glad you found it helpful! Thanks for watching!
Thanks. I just used this at work on the fly. Great directions.
Awesome! Glad you found it helpful! Thanks for watching!
You're so clear and intuitive. I work in HR as well, I wish I could work for you lol. Btw, may I ask, what software do you use to create the small animations you have, such as screen transitions, the animated mouse click, etc.
You save my life. Thank you.
Glad to help! Thanks for watching!
awesome video, just what I needed. Thank you very much!)
So glad to hear you found it helpful! Thanks for watching!
wow! finally exactly what I was looking for. You explained it very clear and not too much jumping all over the place. Thank you!
Glad you found it helpful! Thanks so much for watching!
Excellent video. Thanks for posting. I'm trying to create a sheet that returns hockey players names while attached to a shots taken parameter. I'm that much closer now.
Cool!! Glad you found it helpful! Good luck with your spreadsheet! Thanks for watching!
Very useful! Thank you!
Glad you found it helpful! Thanks for watching!
Excellent. Easy to understand. Thanks
So glad to hear you found my video helpful! Thanks for watching!
I actually did a thing! Thank you!
👍 Glad to help! Thanks for watching!
Very helpful. Thank you!!
Glad you found it helpful! Thanks for watching!
Very easy to follow! Best video tutorial!
Glad you found it helpful! Thanks for watching!
I would like to put all the employee code from sheet 1 to sheet 2
My problem between 2 sheets:
The employee spelling names are slightly different and both sheets didn't have the same unique ID
Thank you! Needed help with this
Glad you found it helpful! Thanks for watching!
Thanks sharon.... This is great
Glad you found it helpful! Thanks for watching!
Amazing Video, It was very impactful, thank you
Glad you found it helpful! Thanks for watching!
Thank you, thank you, thank you 😅 🙏
You are very welcome, thanks for watching!
Important note: when you have a spreadsheet with a lot of columns: just make sure that the column with the lookup value is simply left of the column you want to search in. The Vlookup function can only search to the right! Good luck 😊
Yes, great tip! Thanks for sharing. If you are unable to rearrange your data to have your lookup value to the left of your lookup range, then consider using XLOOKUP. Here is a video that highlights the difference and limitations of VLOOKUP. Thanks for watching! ruclips.net/video/RPYbfRgYfvk/видео.html
Very good explanation, expecting more videos, Thanks @sara
Thanks so much! Glad you found it helpful! Thanks for watching!
Another great video!
Thanks so much for watching, Donna! Glad you found it helpful!
Thank you so much ;)
You are very welcome! Thanks for watching!
Thanks.
Thank for watching!
Thanks!
Thank you so much for your Super Thanks and donating to my Channel. I appreciate it very much and am so glad you found this video helpful! Best of luck to you!
yes, its easy steps and simple,could you also show how to get unique numbers using Vlookup? please
Nice, thanks! Quick Question why did you create Matched Names in the Master Sheet and not in the other sheet?
THANKS FOR SHARE
You're welcome! Thanks for watching!
Nice and informative
Thanks for watching, Syed! Happy New Year to you!
Thnx a lot
You are most welcome! Thanks for watching!
I have names as an identifier. I have to see either students have submitted their assignment or not . I have the master sheet. And the data sheet of each assignment is downloaded through a system
Hi Sharon, That's amazing video! Is there a way to search a list of ids suppose 20 ids in one worksheet and another worksheet holds a database where we need to search for those 20 ids and highlight when found.
Yes - you can do this different ways. I would recommend using a Match formula and then apply conditional formatting to highlight the matching values. I will work on a video on this topic. Thanks for watching!
Hi I would like to know how to autocomplete names after typing the first three letters in an excel sheet from a list of names in a second excel sheet. Thank you
What if the 2 sheets have the same identifier, but not the same first column?
Thankyou for this video, I just wanted to ask what if the datas are not in the same order or a dataset where it would not be possible to sort them in a particular way like from a to z or something, how would we go about it then cause this conditional formatting and v look up only works when the data set is ordered in both of the workbooks or sheets.
Hi Vishal! If you have Microsoft 365, I recommend using XLOOKUP instead of VLOOKUP so you will not have these sorting issues. But if you don't have access to XLOOKUP, here are some things to consider.
If the data sets you're working with are not in the same order or cannot be sorted in a specific way, you can still use VLOOKUP with some modifications to achieve the desired result. Here are a few approaches you can consider:
Use a helper column: Insert a new column next to the data set you want to perform the VLOOKUP on. In this helper column, create a unique identifier for each row, such as concatenating key values from multiple columns. Then, perform the VLOOKUP using this helper column as the lookup value. This way, even if the order of the data changes, the unique identifiers will remain the same.
Use INDEX-MATCH instead of VLOOKUP: Instead of using VLOOKUP, you can use the combination of INDEX and MATCH functions. The MATCH function will locate the position of the lookup value in the data set, and the INDEX function will return the corresponding value from a specified column. This method allows you to perform the lookup regardless of the order of the data.
Sort the data using a helper column: If it's acceptable to temporarily sort the data, you can create a helper column with a sequential number or any other identifier that maintains the original order of the data. Then, sort the data sets using the helper column. Perform the VLOOKUP with the sorted data, and once you have the desired results, you can revert to the original order by sorting them back using the helper column.
Utilize a unique identifier: If you have a unique identifier that is common to both data sets, you can use it as the lookup value in the VLOOKUP formula. This way, even if the order of the data changes, the lookup will still find the correct matching values based on the unique identifier.
Remember, when using VLOOKUP or any other lookup function, the key is to have a consistent and unique identifier that can be used to match the values between the datasets. By implementing one of these approaches, you can perform the lookup operation even when the data sets are not in the same order or cannot be sorted in a specific way.
I hope this helps! Thanks for watching!
Thanks for video. can we use Google sheet of mb if yes pl make video how to do on it.
How would you do it if the identifier isn't in the same column of the worksheets? My Identifier is in Column K in worksheet 1 and Column E in worksheet 2
Hi. Hope you could help me. I have 2 name lists on separate spreadsheets, next to the names on the first sheet theres a text, if the name matches i want the same text to be added on the second spreadsheet next to the matched name. Please help
I am trying to fill in a CRM with incomplete info (don't have all email addresses, and they don't match with names listed on .csv file). Any tips on how I can use either VLookup or any other function to fix a LinkedIn export? I imagine it's a somewhat common issue but WOW is this an annoying one hah. Thanks in advance!
I am having issues using lookup following your tutorial. I did match name successfully, when Insaved both file, the formula gone. Could you please help on this? Thank you
What farmat should we select for both the columns to vlookup words
Would this work if you had names missing from one sheet? Like if you were trying to figure out what names from one spreadsheet are missing from another sheet?
Hi Krystl! Yes, when you use VLOOKUP, if it does not find a match, it will return "#N/A" in the cell. You could then filter by #N/A to see who was missing. I hope this helps! Thanks for watching!
i want a solution for similar data of two column in such a way that similar data of two coloumn placed automatically in front of second column and mis matched data will placed below this is there any formula for this?
Madam, how to highlight the cells on which formula applied.
suppose for eg.
A B C D
20 10 10 =sum(A1:A3)
30 40 20
40 50 40
here I want highlight at D1 as well to A1 to A3.
please reply.
My problem is I don't have unique identifiers, Idk if this is even possible.
Hi Alli, for VLOOKUP in this scenario, you do need to have an identifier that the formula can search for so it can return the correct value as needed. I hope this helps! Thanks for watching!
Is there a reason why n/a would show even though it is a duplicate?
just a question, why we can't use the email as unique id?
how to name combination matching two sheet but second commination name in horizontal but same cell name match to first sheet
Hello mam
I want to match Asset number two different spreadsheet
I would like to request your assistance. I have a sheet with just people’s names and emails. The emails are not in any order. Can you please help me match the emails to the names they belong too? All of the emails contain the person’s first and last names as well.
i have two spared sheets one is called stock with price
and another is called calculator
in a stock sheet there is 3 columns category and sub category and price
that shows in calculator sheet in list form
i am not able to get a specific price of category and subcategory wise
ex fruit is category and apple is sub category and 100 is price
in calculator sheet fruit and apple are showing but how can i get the price of it
please help me............
looking to hear from you..
😮
Thanks for watching!
Hi Sharon. My name is Shubham. And I require your little help in excel. Can you do that favor for me. Could you help me any technique we use formula to sort data except using filter. I want to sort data with particular formula not using filter option.
can the userID contain a space?
I have tried this formula so many times, but it's just not working. I don't know what is it that I am doing wrong😢😢😢
Here are a few things to check:
Make sure Column A on both sheets is the lookup value and that if you are looking up based on an ID number that the column on both sheets is formatted as a number, not text. What error are you getting?
How can I know how many times a name of sheet 1 appears on sheet 2?
Hi! You can use COUNTIF to do that. Create an empty column next to the names on Sheet 1 and put this formula:
=COUNTIF(Sheet2!A:A, A1)
This assumes the names in Sheet 2 are in column A and assumes the names on Sheet 1 are in Column A. You can adjust if needed, but this will give you a count of how many times the name appears on Sheet 2. You can copy the formula down. I hope this helps! Thanks for watching!
When I put in false for the Range_lookup, my answer disappears. Could you help me?
Hi Carl! I wish I could see your spreadsheet to help you, but without knowing exactly the data sets and formulas you are working with, here are a few things you can review to try and fix when your answer disappears when you set the Range_lookup parameter to False:
Check the sorting order: Ensure that the data range being searched by VLOOKUP is sorted in ascending order by the lookup column. VLOOKUP requires the data to be sorted for an exact match when using FALSE as the Range_lookup value.
Confirm the exact match: Double-check that the lookup value in the formula exactly matches the value in the lookup column. VLOOKUP is case-sensitive, so even a slight difference in letter casing can cause the formula to return an incorrect result or show as an error.
Verify the data format type: Ensure that the data format type of the lookup value matches the data format type of the values in the lookup column. For example, if the lookup column contains numbers, make sure the lookup value is also a number, not a text or vice versa.
Expand the column width: Sometimes, the answer might be there, but the column width might be too narrow to display the result. Try manually adjusting the column width to see if the value becomes visible.
Check for leading or trailing spaces: The lookup value or the values in the lookup column might have leading or trailing spaces. Trim both the lookup value and the values in the lookup column using the TRIM function to eliminate any extra spaces that might cause the match to fail.
Inspect for hidden characters: There could be hidden characters in the lookup value or the values in the lookup column. You can try using the CLEAN function to remove any non-printable characters.
I hope this may be helpful to you! Thanks for watching!
I have two excel sheet. Sheet 1 contains Column A = Serial Number, Column B = Candidate Name, Column C = Post Name, Column D = Date of Birth, Column E = Category (like UR, OBC, SC, ST) and Sheet 2 Column A = Serial Number, Column B = Post Name, Column C = Required Minimum Date of Birth Date for the Column B Post Name, Column D = Maximum Date of Birth for UR Category, Column E = Maximum Date of Birth for OBC Category, Column F = Maximum Date of Birth for SC Category, Column G = Maximum Date of Birth for ST Category. Please help to find Candidate Age Eligibility at sheet 1 Column F with matching Post Name, Category and Date of Birth.
how do vlookup for name
For example :Jenny,Koo Wen with Koo Wen, Jenny?
Hello - since VLOOKUP needs to reference the "same" field on two different worksheets, and you have name formatted first, last on one sheet, and then last, first on another sheet, then I recommend that you modify one of the sheets to match the other. You can use Text to Columns to split first and last name into 2 columns and then use concatenate to re-combine them in the right order to match the other sheet. Then you can use VLOOKUP to match other data or compare. This Excel org chart video will show you how to format names in Excel: ruclips.net/video/Kq7oq_7vY08/видео.html Hope this helps! Thanks for watching!
Fakkkkkk I don't get it! 😮💨 in my brain it makes sense... the formula is not working on my spreadsheets
didn't work for me, the entire vlookup command just shows up in the cell
Hi Tony - two things to check if the VLOOKUP formula is showing in the cell instead of the result:
1) Ensure the formatting of the cell is not set to "Text"
2) Go to the Formulas tab - Formula Auditing area - and make sure the "Show Formulas" button is not selected (if so, deselect it).
Hope this helps!
i see in youtube that you all using employee number as identifier, how about using employee names instead.
Some employees have the same name.
ex. a1=kishan, n wadhera, t devid, v kohali, faf du, c green, p chawal, a deep, k sharma, r toply, h patel
a2= kishan, t varam, v kohali, s yadav, c green, maxi, a khan, p chawal, r toply, j archar
a3= kishan, r sharma, v kohali, s yadav, c green, a khan, a deep, r toply, h patel, m siraj
a4 kishan, t devid, v kohali, s yadav, s ahmeada, c green, k sharma, r toply, h patel, m siraj
a5 kishan, t varam, v kohali, faf du, c green, a deep, k sharma, r toply, h patel, j archar
a6 d kartik, kishan, r sharma, v kohali, s yadav, c green, p chawal, k sharma, r toply, m siraj
a7 kishan, r sharma, v kohali, s yadav, m bracewl, c green, p chawal, a deep, r toply, h patel this type name in cell
Thanks!
Thank you so much for your Super Thanks and for supporting my Channel! I appreciate it so much. I’m glad you found my videos helpful. Thanks for watching! Best of luck to you!