Want to take your form-building skills to the next level? 🚀 Checkout my KZitem Course on "Creating Fillable Forms in Microsoft Word: A Step-by-Step Guide," kzitem.info/door/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 KZitem 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 KZitem 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 KZitem Creators who have also launched Courses by going to their Channel page and clicking on the "Playlist" tab. Enjoy!
@alliclarkkk
Жыл бұрын
My problem is I don't have unique identifiers, Idk if this is even possible.
@SharonSmith
Жыл бұрын
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!
@adrianpramanta12
Жыл бұрын
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
@kathya5569
Жыл бұрын
Thank you. I have watched probably 15 of these videos and yours is the first one that finally made sense in my brain.
@SharonSmith
Жыл бұрын
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!
@Jizzandapuss
3 ай бұрын
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.
@SharonSmith
2 ай бұрын
Cool!! Glad you found it helpful! Good luck with your spreadsheet! Thanks for watching!
@carlsmallssr.807
Жыл бұрын
When I put in false for the Range_lookup, my answer disappears. Could you help me?
@SharonSmith
Жыл бұрын
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!
@AuntMaeVlogs
Жыл бұрын
What if the 2 sheets have the same identifier, but not the same first column?
@alinagirea8432
8 ай бұрын
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😢😢😢
@SharonSmith
8 ай бұрын
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?
@salimajindani4818
2 жыл бұрын
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
@dianasandoval6378
Жыл бұрын
How can I know how many times a name of sheet 1 appears on sheet 2?
@SharonSmith
Жыл бұрын
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!
@kaitlynbennett8826
Жыл бұрын
I reference this video all the time. Thank you for the clear instructions.
@johnvaldivia4718
3 ай бұрын
Fakkkkkk I don't get it! 😮💨 in my brain it makes sense... the formula is not working on my spreadsheets
@didgeridookapooka8902
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
@chuckie826
Жыл бұрын
This is super super helpful. Thank you so much! I have 5000 records to check and match
@SharonSmith
Жыл бұрын
Glad you found it helpful! Thanks for watching!
@tyqqueecarter6176
Жыл бұрын
Thanks you have helped me as I am a recent graduate working payroll.
@SharonSmith
Жыл бұрын
Congratulations on your new position in payroll! So glad to hear my video helped you. Thanks for watching!
@ThomasRomanLLC
Жыл бұрын
Thank you, Sharon! You solved a major situation for us with a database export from two tables. A HUGE THANK YOU from over here!
@SharonSmith
Жыл бұрын
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!
@samorris21
8 ай бұрын
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
@ngyinlee104
Жыл бұрын
how do vlookup for name For example :Jenny,Koo Wen with Koo Wen, Jenny?
@SharonSmith
Жыл бұрын
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: kzitem.info/news/bejne/rKdt1aWVcKiKY20 Hope this helps! Thanks for watching!
@xaviernj
2 жыл бұрын
didn't work for me, the entire vlookup command just shows up in the cell
@SharonSmith
2 жыл бұрын
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!
@vishalthakwani9265
Жыл бұрын
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.
@SharonSmith
Жыл бұрын
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!
@ExcelEssentials_
11 ай бұрын
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 😊
@SharonSmith
11 ай бұрын
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! kzitem.info/news/bejne/s4aPyJqIoIuXqaA
@mrsouzzo
Ай бұрын
OMG... thank you so much... I have spent hours looking for this solution and I finally found it!!!
@SharonSmith
Ай бұрын
I’m so glad you found my videos and that they helped you out! Thanks for watching!
@masoodanwer762
Жыл бұрын
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?
@Kevinfantasyguru
10 ай бұрын
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
@shoppersdream
2 ай бұрын
Nice, thanks! Quick Question why did you create Matched Names in the Master Sheet and not in the other sheet?
@krystlnickel
Жыл бұрын
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?
@SharonSmith
Жыл бұрын
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!
@Kevinfantasyguru
10 ай бұрын
how to name combination matching two sheet but second commination name in horizontal but same cell name match to first sheet
@soniasmelody2709
Жыл бұрын
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
@aliemmanuel8233
Жыл бұрын
Amazing Video, It was very impactful, thank you
@SharonSmith
Жыл бұрын
Glad you found it helpful! Thanks for watching!
@nwadianiangela6568
Жыл бұрын
Thank you, the video was beneficial and the explanation was straight to the point.
@pheladimojela-ec5ik
7 ай бұрын
Hello mam I want to match Asset number two different spreadsheet
@arikkatz6611
6 ай бұрын
awesome video, just what I needed. Thank you very much!)
@SharonSmith
6 ай бұрын
So glad to hear you found it helpful! Thanks for watching!
@vamsireddy5051
Жыл бұрын
Very good explanation, expecting more videos, Thanks @sara
@SharonSmith
Жыл бұрын
Thanks so much! Glad you found it helpful! Thanks for watching!
@DevSharma-fj9ue
Жыл бұрын
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.
@SharonSmith
Жыл бұрын
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!
@TheCastleberryGirls
6 ай бұрын
Is there a reason why n/a would show even though it is a duplicate?
@201Bmoney
Жыл бұрын
This is Outstanding Sharon!!! I needed to cross-reference 2 spreadsheets. This saved me hours of frustration.
@SharonSmith
Жыл бұрын
So glad to hear you found it helpful and it is saving you time! Thanks for watching!
@seymurmehdiyev7114
6 ай бұрын
Thank you Sharon!It was helpful for me.I understood you easly
@SharonSmith
6 ай бұрын
Glad to help! Thanks for watching!
@sdharris10
2 жыл бұрын
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
@SharonSmith
2 жыл бұрын
Glad you found it helpful, Scottie! Thanks for watching and talking time to leave a nice comment. I appreciate it!
@artfun7907
2 жыл бұрын
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..
@harsinchh4342
Жыл бұрын
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.
@PraveenKumar-ig5sf
Жыл бұрын
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.
@cholley5433
Жыл бұрын
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.
@Nondescripthumanoid
Жыл бұрын
Thanks. I just used this at work on the fly. Great directions.
@SharonSmith
Жыл бұрын
Awesome! Glad you found it helpful! Thanks for watching!
@zevlagable
11 ай бұрын
i see in youtube that you all using employee number as identifier, how about using employee names instead.
@ut_punkn1859
10 ай бұрын
Some employees have the same name.
@MargieGonez
7 ай бұрын
just a question, why we can't use the email as unique id?
@dreamca9412
2 жыл бұрын
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.
@niyipossible979
Жыл бұрын
Excellent. Easy to understand. Thanks
@SharonSmith
Жыл бұрын
So glad to hear you found my video helpful! Thanks for watching!
@douglascorley7364
2 жыл бұрын
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!
@royoneto5156
2 жыл бұрын
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!
@SharonSmith
2 жыл бұрын
That is awesome, Roy! So glad to help. Thanks for letting me know and for watching my videos. Take care!
@Spud_E_Buddy
2 жыл бұрын
I forgot how to do it, and had to come back again to watch your video 😂 🙏
@SharonSmith
2 жыл бұрын
Awesome - thanks for watching it again! Glad you found it helpful!!
@masterpiece_terez
Жыл бұрын
Thank you! Needed help with this
@SharonSmith
Жыл бұрын
Glad you found it helpful! Thanks for watching!
@AmeliaTjekka
Жыл бұрын
Thank you so much ;)
@SharonSmith
Жыл бұрын
You are very welcome! Thanks for watching!
@VinouRajesh-e3p
Жыл бұрын
yes, its easy steps and simple,could you also show how to get unique numbers using Vlookup? please
@marklykkebak8104
Жыл бұрын
I actually did a thing! Thank you!
@SharonSmith
Жыл бұрын
👍 Glad to help! Thanks for watching!
@Upgradewithsarah
8 ай бұрын
Very helpful. Thank you!!
@SharonSmith
8 ай бұрын
Glad you found it helpful! Thanks for watching!
@stamatisvassiliou8845
Жыл бұрын
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
@Akolkar2
Жыл бұрын
Thanks for video. can we use Google sheet of mb if yes pl make video how to do on it.
@nickaraiz1490
4 ай бұрын
Very useful! Thank you!
@SharonSmith
4 ай бұрын
Glad you found it helpful! Thanks for watching!
@harisr6270
Жыл бұрын
Thanks sharon.... This is great
@SharonSmith
Жыл бұрын
Glad you found it helpful! Thanks for watching!
@liverpool4everth18
Жыл бұрын
You save my life. Thank you.
@SharonSmith
Жыл бұрын
Glad to help! Thanks for watching!
@prajwal.m8337
Жыл бұрын
What farmat should we select for both the columns to vlookup words
@Spud_E_Buddy
2 жыл бұрын
Thank you, thank you, thank you 😅 🙏
@SharonSmith
2 жыл бұрын
You are very welcome, thanks for watching!
@virlonetm6843
2 жыл бұрын
Very easy to follow! Best video tutorial!
@SharonSmith
2 жыл бұрын
Glad you found it helpful! Thanks for watching!
@alharshaniA
2 жыл бұрын
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!
@RejwanRajibbd
Жыл бұрын
THANKS FOR SHARE
@SharonSmith
Жыл бұрын
You're welcome! Thanks for watching!
@Kibra150
2 ай бұрын
Thank you!!!
@SharonSmith
2 ай бұрын
Glad to help! Thanks for watching!
@mikew2610
Жыл бұрын
Thanks.
@SharonSmith
Жыл бұрын
Thank for watching!
@syedikramullah1114
2 жыл бұрын
Nice and informative
@SharonSmith
2 жыл бұрын
Thanks for watching, Syed! Happy New Year to you!
@okj1906
10 ай бұрын
Thanks!
@SharonSmith
10 ай бұрын
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!
@donnaryan3162
2 жыл бұрын
Another great video!
@SharonSmith
2 жыл бұрын
Thanks so much for watching, Donna! Glad you found it helpful!
@amyalvar
Жыл бұрын
😮
@SharonSmith
Жыл бұрын
Thanks for watching!
@davimachen9039
8 ай бұрын
Thanks!
@SharonSmith
8 ай бұрын
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!
@iClone316
2 жыл бұрын
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.
@NikspritTravel360
Жыл бұрын
No BS, straight to point. Clear and concise
@SharonSmith
Жыл бұрын
Glad you found it helpful! Thanks for watching!
@excellover7172
2 жыл бұрын
Thnx a lot
@SharonSmith
2 жыл бұрын
You are most welcome! Thanks for watching!
@hayleyhemming2647
Жыл бұрын
can the userID contain a space?
@Moon20791
2 жыл бұрын
wow! finally exactly what I was looking for. You explained it very clear and not too much jumping all over the place. Thank you!
@SharonSmith
2 жыл бұрын
Glad you found it helpful! Thanks so much for watching!
Пікірлер: 125