How to Use VLOOKUP in Excel (free file included)

Подписаться 2,6 млн

Просмотров 121 тыс.

50% 1

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Excel VLOOKUP is one of the most essential functions in Excel. In this tutorial for beginners you'll learn how to properly use Excel VLOOKUP without mistakes. You’ll learn how to quickly find information from different sheets, get exact or closest matches, and troubleshoot common errors. VLOOKUP Excel saves you time and reduces mistakes by automating data searches across your spreadsheets.
⬇️ Download the free practice workbook here: pages.xelplus.com/vlookup-exc...
00:00 VLOOKUP Excel
00:50 How to Do a VLOOKUP Formula
05:29 VLOOKUP Across Sheets
07:09 VLOOKUP Approximate Match
09:14 VLOOKUP Not Working
11:29 NA Error in VLOOKUP
12:18 Get rid of NA Error in VLOOKUP
14:31 XLOOKUP Excel
We start with the basics: how to do a VLOOKUP to find related information, like a customer’s profession from another dataset. You’ll learn how to set up your data range correctly and discover the importance of fixing the table array to avoid shifting ranges when copying formulas. This practical VLOOKUP example shows you the essentials of how to use VLOOKUP effectively.
We also dive into advanced VLOOKUP techniques, including how to use VLOOKUP across different sheets, how to find exact matches with the FALSE parameter, and how to handle approximate matches efficiently. Plus, we tackle common VLOOKUP errors and show you how to correct them using simple adjustments and functions like TRIM and IFNA.
By the end of this video, you'll be equipped to use the VLOOKUP formula confidently. You’ll understand why it’s a go-to tool for Excel users seeking to streamline their data analysis tasks.
📨 Subscribe to "Between the Sheets" newsletter to stay on top of office tips and Excel hacks: link.xelplus.com/yt-d-newsletter
➡️ Join this channel to get access to perks:
/ @leilagharani
🎬 LINKS to related videos:
How to Use the NEW & IMPROVED Excel XLOOKUP: • How to Use the NEW & I...
How to do a Left Lookup in Excel: • How to do a Left Looku...
👕 Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
More resources on my Amazon page: www.amazon.com/shop/leilagharani
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel

Опубликовано:

15 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 249

@LeilaGharani Месяц назад

Download the file I used in the video from here 👉 pages.xelplus.com/vlookup-excel-file

@idreesbahrain9093 4 дня назад

Madam Leila Help me plz my ukhty i have an inventory sheet in excel, sheet no. 1 is product list, sheet no. 2 is opening stock, sheet no. 3 is Incoming product, sheet no. 4 is sales product it means outcomin product , i want to know how to write the price you entered according to the date i bought the load together with the price its unchanged at the other price i started writing in the beginning Help plz my sister Bahrain From Tanzania

@jon4 Месяц назад

Really appreciate how you broke down the importance of using 'FALSE' for exact matches in VLOOKUP. It’s one of those things you don’t realize the impact of until you get those frustrating errors.

@jerry5149 Месяц назад

You are always so concise and professional!

@MrsJerjev 8 дней назад

Thank you very much for your time and patience in explaining the ins and outs of the spreadsheet. I am grateful.

@christianwibowo6420 Месяц назад

Years using vlookup and still you can teach something new , that approximate function really blows my mind, thanks a lot Leila, you are The best

@LeilaGharani Месяц назад

Glad it was helpful!

@jpv1980 Месяц назад

After so many years using Excel, I thought I knew the VLOOKUP function... there's always something new one can learn.

@LeilaGharani Месяц назад

Great to hear!

@golden-joeosegbe866 Месяц назад

Thank you Leila. You are indeed a great teacher. I have never gotten VLookup right all my career no matter how many demos I've watched. Thanks a million.

@LeilaGharani Месяц назад

You're very welcome! I hope this video was helpful.

@anjelas2708 Месяц назад

Feels like we are going back in time... I thought this was an old video... Surprised to see it was posted 4 hours ago

@LeilaGharani Месяц назад

haha yes. It's a new upload. Even though I've exclusively switched to XLOOKUP, I realized there are many in this world that don't have access to XLOOKUP and still need to use VLOOKUP but struggle to work with it. My old videos are outdated and don't cover multiple aspects so I wanted a more comprehensive video to share.

@anjelas2708 Месяц назад

Yes many times we have to work with vlookup... I switched to xlookup fully last year only because I used to write formulas for others who didn't have access to xlookup... But after switching to xlookup it's hard to go back to vlookup...

@vijayarjunwadkar Месяц назад

Hmmm, finally the "Thursday" video back after long time! Thanks Leila! 🙂 You explained it in your unique style making it easy to understand by anyone. While I "knew" most of it, I did pick up a "new" thing or two for sure! Keep those coming...

@LeilaGharani Месяц назад

This for tuning in, Vijay! I'm taking it a bit slower these days. 😁

@bheardnow6410 Месяц назад

Never thought of using Trim in the lookup value. Thanks!

@ronschenk5024 11 дней назад

Good Morning Leila. I am a consultant for a major phone company and work with spreadsheets daily. I have to THANK YOU for all your extremely informative videos. Any time I have formula issue I go straight to your RU-vid site to solve it. I cant tell you how many times I have been on here since getting into Excel. THANK YOU again and can't wait for the next video!!

@wmfield152 Месяц назад

All one needs to know to use VLOOKUP correctly. Thanks Leila for this quality presentation.

@Rainman___ 7 дней назад

Awesome video as always! Very engaging. I watched ur old vlookup tutorial a couple years ago. I'm glad you've added this refreshed version.

@LeilaGharani 6 дней назад

Awesome, thank you!

@phongluunguyen4177 Месяц назад

Thank you, your lessons is probably the most easy to understand on RU-vid, thank you so much ♥

@vincentcollin6490 Месяц назад

As clear as ever! And as understandable as ever for a French person!

@happymystic9800 Месяц назад

Nicely explained and a great reminder of the basics of this function which also supports the principal understanding of how Excel functions typically work. Judging some of the complaining comments tells me that even VLOOKUP is way too complex for them.

@idwtv534 Месяц назад

The lighting and camera quality has improved a lot. Good job

@prachidugal6927 Месяц назад

Trim & IfNa are the new takeaways for me. Thank you Leila.

@syedal-mehedyhasan3826 Месяц назад

Learnt a lot from you videos, Thanks. ❤

@dineshkumar-vy7jk Месяц назад

Useful tips Very informative ❤❤

@sarmadpirzada8485 Месяц назад

Thanks for detailed Video about Vlookup and its Errors

@aleksandrsaleksandrs4761 Месяц назад

You are fantastic! Thank you so much, your lessons are the most precise and I was able to understand this function only because of your approach. Fantastic!❤

@LeilaGharani Месяц назад

Great to hear!

@queenkai9522 Месяц назад

Thank you. Very valuable content, as always.

@simplicity-_- Месяц назад

Your vids always so informative. Thanks. I've been using vlookup for years, but I learned from this vid the use of 0/1 instead of FALSE/TRUE, and that trim function usage will defo help in huge database. Thanks again ...

@tufany3847 17 дней назад

Thank you for the video! Very informative and helpful.

@DeborahFont Месяц назад

This is an amazing video! I am in awe of how you can be so concise and so thorough at the same time. Your explanations not only help me with VLOOKUP, but also with understanding better how Excel formulas work in general. Thank you!

@LeilaGharani Месяц назад

Glad it was helpful!

@yazid6058 Месяц назад

Always great, using Trim in the Vlookup function is what I was missing, thanks

@giorgidvali951159 Месяц назад

Love all Your videos

@Miriam-jy7tgz 26 дней назад

Thank you!!!!

@meenagohil5 Месяц назад

Very well explained.❤

@Aman_Mehta_Coding 19 дней назад

It's a really great informative video 👍🏻

@LeilaGharani 18 дней назад

Thank you!

@realopal1 Месяц назад

Love this!

@SyedMuzammilMahasanShahi Месяц назад

Thank you so much for this amazing video.

@r.h.7633 Месяц назад

Thank you!!

@shivshaktisharma2914 7 дней назад

THANK YOU

@fahadsharef610 Месяц назад

Nice one Leila! The common errors and solutions at the end was very useful.

@LeilaGharani Месяц назад

Thank you! 😃

@koponi100 Месяц назад

looking great Leila💙

@VahabSahvi Месяц назад

Excellent ❤

@learningwithmaliha Месяц назад

Super nicely explained all the issues faced by the user using Vlookup.

@rajvardhanchauhan8232 День назад

Thanks a lot for explaining in such detail.

@LeilaGharani 23 часа назад

Our pleasure!

@ArunKumar-ju4jb Месяц назад

Thanks 👍

@user-eo5pr8cx7w Месяц назад

Thanks for video ❤

@SyedMuzammilMahasanShahi Месяц назад

Thanks for the share.

@wwxxxyz Месяц назад

thank you so much!

@TheDigitalOne Месяц назад

PRETTY useful function that I did now about and that I haven’t needed to use, but it does save a lot of time when searching and transferring data from one cell to another, but then again if the function is used incorrectly, then it can mess all the data completely. Thanks! 😺🥰🙋‍♀️⭐️💎

@gauraogadkari7206 17 дней назад

Perfect explanation mam❤❤

@MohitYadav-vk8sq Месяц назад

Many Thanks Leila, Whatever I know about Ms Excel just cause of your tricks. Thanks again 🙏 Just a little wish from my side could you please start playlist for Ms Excel interview oriented questions and answers...

@ashutoshshete4942 Месяц назад

Leila, learnt a lot from you. Couldn’t get why you are making a video on such a basic topic

@LeilaGharani Месяц назад

I know. My team convinced me that basic topics are important and we are getting many questions still on VLOOKUP. I didn't want to send people to my old videos because they're outdate, hence this video 🙂

@user-kp7cw5hl3f Месяц назад

Thank you Leila, great topic as always

@LeilaGharani 27 дней назад

Glad you liked it!

@bharatrajchhimpa7777 29 дней назад

Great ❤

@DY357LX 20 дней назад

Thanks for this Leila! It's been a huge help with my work projects.

@LeilaGharani 18 дней назад

You're so welcome!

@ExcelVisual Месяц назад

This is an interesting solution, thanks.

@gabrielentrada2631 26 дней назад

Amazing

@momodousowe3034 Месяц назад

Thanks as always. Simple and straight to the point. Could you kindly a vedio on how you records your vedios, tools used, the annotations etc.?

@johansanjaya3891 Месяц назад

basic, but really important. nice

@OSHOFIRST 22 дня назад

Excel Is On The Fire 💥💥💥

@hemvaidya Месяц назад

Wonderful Magic

@sabrunson Месяц назад

Thank you so much for your training. Breaking it down into easy to understand steps like this helps us with ADHD. Thank you.

@LeilaGharani Месяц назад

Glad it was helpful!

@arthiff6059 Месяц назад

This is an excellent beginner explanation video, and I wish I had this type of video when I first learned VLookUp, However, using the Trim function within the lookup is new to me. Always learn something new from you. Thank you very much for your great effort.

@LeilaGharani Месяц назад

Thanks for tuning in!

@singhcomputercourses Месяц назад

Great 👍

@WaseemAhmad-ux1nz Месяц назад

Your videos are awesome

@LeilaGharani Месяц назад

Glad you like them!

@WaseemAhmad-ux1nz Месяц назад

@@LeilaGharani please make a video how can we make full accounts from voucher to Financial statements in Excel for SMEs. Also suggest me that from where I can get free pre designed formats for detail budgets

@arainmk 18 дней назад

There is always something more to learn

@LeilaGharani 17 дней назад

That's exactly what we stand for here. 😁

@johnwilson7845 Месяц назад

Oh I soooooo don’t miss V-LKup 😊. Very nice video, thanks!

@LeilaGharani Месяц назад

Thanks! 😃

@sawdakhan Месяц назад

Excellent! Hope all vlookup problem will be solved.

@LeilaGharani Месяц назад

I hope so too! Thanks for watching!

@YoussefTsun Месяц назад

I couldnt get the space trick, but i surely know how to solve its problem... this vid didnt add to me anything new, but i had to say that the test parts in your video made me engaged. It didnt add to me bcz i already saw many of your videos b4 Thanks alot, keep it up

@fedixafrika5874 Месяц назад

Awesome! I have had errors like "spill" and "value". I will try with my file and see if I come right. Thank you so much.

@LeilaGharani Месяц назад

If you have errors with #spill then it means you have XLOOKUP function. That's easier to work with than VLOOKUP. Check out my videos on that.

@mazinazam Месяц назад

Brilliant as usual Leila :) Honestly, I prefer to use the XLOOKUP rather than the VLOOKUP.

@LeilaGharani Месяц назад

👍

@vikastiwari7134 Месяц назад

Very nice video & very beautifully hair cut, Love from Bharat

@edilpoulina1870 Месяц назад

GR8 explanation, it was refreshing and enjoyed it. I use xlookup. BTW, nice looking (new hair style?)!

@LeilaGharani Месяц назад

Thanks! 😃

@sundertalreja7208 Месяц назад

It feels like going back to summer of 69 😂 after using xlookup and filter function, I have almost stopped using vlookup.

@LeilaGharani Месяц назад

So did I 🙂 - but unfortunately the world still is filled with Excel files that have VLOOKUPs

@thomasko31 Месяц назад

⁠@@LeilaGharani same video but for xlookup next 😉

@artofsalsa5268 Месяц назад

One other error that might occur: If the lookup table has more than one item with the same content, in the column - the VLOOKUP function will return the first item it finds. For example a lookup table has name John Smith twice in the table column (as the company has two people with that name). Obviously better to search another field in the table that's more unique, like Employee ID etc. So sometimes for a large dataset it might be worth doing a quick pivot table to do a count of names - to spot if it repeats (or use COUNTIF)

@MarionKentish Месяц назад

I use vlookup to identify new values in an export file that weren’t in the previous export file. I find that looking up a single column that contains the unique identifier is faster using vlookup as I can type the ending TAB 1 TAB 0 ENTER then filter for N/A results (ie new transactions). I don’t have to use the mouse again to select the second lookup range. But besides this single use case, I always use Xlookup these days

@Saltcoteplace 29 дней назад

Excellent video thank you. I’m looking for a catering solution for the kitchen. Wedding Guests use Google forms to get dietary requirements and then add table number and the kitchen use Vlookup to get a list of who is on which table with dietary preferences.

@ShaunakHub Месяц назад

Can you do a video on real life usage of HLOOKUP ? I am curious to know the real life usage of this function. Have been using spreadsheets for a long time, but yet to find a scenario when I would want to use this function.

@naiveganesh Месяц назад

Can You make a video about power automate in which how we can generate a predefined format as a Pdf or send same pdf as email attachment

@williamjamieson2334 Месяц назад

Use Powe Query instead. Data tab, get data. You can set other Excel documents as data sources and do a lot more in a stable way

@chh8860 Месяц назад

Once again ... uncannily timely ... wonderful to watch and learn with a mug of hot black coffee. But I think for my current need, XLookup is the way to go for me today. Nonetheless, always a treat to have a Leila video on Thursday morning. Thank you ... thank you ... thank you ... 😍😍😍 PS How can Cooper STILL be employed? ... or ... haven't you gotten around to firing him yet?

@LeilaGharani Месяц назад

Life is much easier for those with newer versions of Excel 😊 Many thanks for tuning in!

@SamerTipsAndTricks Месяц назад

Ultimate explain for Filter function

@teoxengineer Месяц назад

Hi Leila, Thank you so much for this tutorial and we have missed VLookup function because of XLookup :) I wanna add some comments about VLooup function.: 1- It can be searched the wanted valur from right to left by using Choose function. Some any cases, I'm using this function in Vlookup to seaech values by determining range sequance and Choose is allowing me to shift ranges in a table like (=Choose({2,1},Range1,Range2) 2- Is it possible to use wildcards in Vlookup function? 3-How can we do column numbet dynamic. As we know that when we insert a column between data range, this column number is changing. 4- Can we search multiple values in a range by using Vlook up? 5- Vlookup bring the first value and it is not allowing to bring the second value for same wanted value. How can we adjust it to extract multiple values which are ranged in a single column?

@luayabdelbaqi7566 Месяц назад

🤗🤗 لقد أجبت على كل أسئلتك نريد منك أفكارا عن استخدام "TRUE" أو "1"، لقد حاولت تجربة ذلك كثيرا ولكن إلى الآن لم أستخدمها بشكل فعال! بالتوقيق والنجاح ليلى

@vla_music_ai Месяц назад

Let’s call this a legacy video. xlookup is the way to go

@aricohen616 Месяц назад

My wife is great with Excel but I am not. This channel is great! Thank you!

@LeilaGharani Месяц назад

Great to hear!

@cajetansoares6496 Месяц назад

Long time no see Leila

@LeilaGharani Месяц назад

Hello - I'm taking it a bit slower these days. 😊

@cristiantilihoi2591 Месяц назад

Please exlain if the VLOOKUP function searches to the left with a negative column number argument? Without INDEX and MACH indoor function VLOOKUP. It is possible?

@mahmoudwasfy8741 Месяц назад

👍👍👍👍👍👍

@user-nn7of8iy7w Месяц назад

❤❤❤

@darylseaton6493 Месяц назад

Hi Leila, thanks much for the video. Your videos are always appreciated. I don't understand the first issue. Doesn't vlooup work off of the vertical position of "Sid?" I don't know how you would get the wrong answer from that example. Could you explain further please? Thank you.

@LeilaGharani Месяц назад

Yes, but default match for VLOOKUP is approximate match - and for it to work correctly you need to have your lookup range - so the range "Sid" is in to be sorted in ascending order. Unfortunately that's just how Vlookup works. To make sure you look for an exact match you need to add the last FALSE in the formula. It would have been easier if Microsoft programmed the function so that default behavior is exact match - so it would find "Sid" no matter how the data was sorted, but unfortunately they didn't do that. They did fix this for XLOOKUP so XLOOKUP by default doesn't care how the data is sorted and will find the right match.

@georgevlahakis5803 Месяц назад

I fully agree w/Leila on Microsoft mistake on Vlookup() to build it with the default value "not_exact_much". Moreover, the intellisense prompt of Vlookup() is not informative at all !! I could say "Misleading" or even "confusing": [range_lookup] for me does not help at all (it could mean "from top to bottom", "from bottom to top" of the range, "exact match" or "not exact match" or anything else the user could imagine. it is "=VLOOKUP(lookup_value,table_array,column_index_num,[range_lookup])", but it should be: =VLOOKUP(lookup_value,table_array,column_index_num,[NOT_EXACT_MATCH]), so that the user knows that the default is "TRUE" or "1" (i.e. NOT_EXACT_MATCH) and the EXACT_MATCH is "False" or "0". I wonder how Microsoft can make such trivial mistakes and (more important) does not change/correct it in the follow-up builds...all these years. For me the intellisense should have been corrected.

@ExcelExplorers786 Месяц назад

I could use your assistance in my work? Could you provide some guidance?🙂🙏

@gasperm3 18 дней назад

After inputing the vlookup formula in for the first excercise, and I try to move the formula down for all the names like you said, Nothing works when I type in F4?

@colinmackie5211 Месяц назад

I use the function extensively. I always name my ranges... much tidier and easier. I often derive a concatenated key for my lookup value and similarly the range left column. I would like to understand the excel "data model" and excel tables based on my knowledge of relational databases?

@LeilaGharani Месяц назад

Great! yes, those are neat techniques for multiple lookups. The data model in Excel is also called Power Pivot. I have a few videos on that and a complete course on my site XelPlus.

@latifahfahad6121 Месяц назад

Please make video how to hundle customers data base, for example recall data for certain customer in a separate individual excel sheet depending to the customer number 😢

@chrism9037 Месяц назад

Thanks Leila, good video. Although to be honest, since XLOOKUP came out, I haven't looked back :)

@LeilaGharani Месяц назад

I can imagine. 😊 Thanks for tuning in, Chris!

@dare2bd Месяц назад

❤

@BaSYaVSkI Месяц назад

I use 360, and after the XLookup, I have never use VLookup again, but before that, of course this was bread and butter )). I am not sure if there are cases where V would be preferred to X.

@steveholt420 Месяц назад

Yikes. Censoring comments. Interesting

@divyv20 Месяц назад

Hey Leila , very good video . I can do better editing in your videos which can help you to get more engagement in your videos . Pls lmk what do you think ?

@SamerTipsAndTricks Месяц назад

Plz we need this video for filter function

@LeilaGharani Месяц назад

Here you go: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Eehk6PC0oGs.html

@sameh1180 Месяц назад

I have an issue and want a solution. I have a chart like waves for prices of one company stock and I want to show on the chart only values of top unique value and down unique value for each wave I made a helper column and used the formula>> IF(AND(F4>F3,F4>C5),C4,"") but the problem there is a similar number at the top and down wave

@deadkiller6050 Месяц назад

can this be done using the index and match functions(approximate part)?

@LeilaGharani Месяц назад

Index Match can do it all 💪

@figueroajose7841 29 дней назад

Why does it populate "head of procrastination" at 4:00 if it doesn't match? Is it because the "3" in the formula is read as "column 3 row 3" without the "false" ? Thank you in advance.

@LeilaGharani 28 дней назад

No, it's because the formula was missing the last argument: [range_lookup]. For an exact match, you need to add False (or 0). By default, VLOOKUP performs an approximate match if you don't include False or 0.