# VLOOKUP Function Beginner to Advanced 26 Examples: How To Use Excel VLOOKUP Function

**Опубликовано: 26 мар 2012**- Download Start File: people.highline.edu/mgirvin/RUclipExcelIsFun/VLOOKUP-SHARK-WEEK-DoawnloadableWorkbook01-Start.xlsx

Download Finished File: people.highline.edu/mgirvin/RUclipExcelIsFun/VLOOKUP-SHARK-WEEK-DoawnloadableWorkbook01-FINISHED.xlsx

Entire Page with All Files for All Videos: people.highline.edu/mgirvin/excelisfun.htm

Then scroll down to this section: "VLOOKUP "Shark" Week Downloadable Workbooks"

Look for the file named: VLOOKUP-SHARK-WEEK-DownloadableWorkbook01-Start.xlsx

Excel VLOOKUP is one of the most important functions because everyone needs to look stuff up. Not only that, but if you want to get a job, you must know VLOOKUP.

In this video learn everything you need to know about VLOOKUP in 1 video.

Here are the topics (minute marks jump you to that part of the video):

1. VLOOKUP = V = Vertical ( min)

2. Why so common? Because everyone needs to look stuff up ( 00:43 min)

3. VLOOKUP Delivers a value to a cell: Extract Match ( 2:37 min)

4. VLOOKUP #N/A and IFERROR or IF ( 5:25 min)

5. VLOOKUP and Data Validation ( 8:10 min)

6. VLOOKUP with Named Range ( 10:34 min)

7. VLOOKUP as Formula Element ( 17:24 min)

8. HLOOKUP = H = Horizontal ( 18:25 min)

9. VLOOKUP Delivers a value to a cell: Approximate Match ( 19:40 min)

10. VLOOKUP with table on different sheet ( 24:00 min)

11. VLOOKUP with table in different workbook ( 25:56 min)

12. Show Full Record: Numbers above column header ( 27:15 min)

13. Show Full Record horizontally: COLUMNS Function ( 30:20 min)

14. Show Full Record vertically: ROWS Function ( 32:28 min)

15. Show Full Record: MATCH Functions ( 33:40 min)

16. Two Lookup Values: Add join column as first column in table ( 35:40 min)

17. Two Lookup Values: Join columns in formula and use CHOOSE function ( 38:50 min)

18. Partial Text Lookup: LEFT and SEARCH Functions ( 43:22 min)

19. Partial Text Lookup: MID Function ( 45:37 min)

20. Partial Text Lookup: RIGHT Function ( 48:08 min)

21. TRIM and VLOOKUP: Space in lookup value ( 48:57 min)

22. TRIM and VLOOKUP: Space in 1st column of table ( 51:03 min)

23. VLOOKUP with 3 lookup tables: CHOOSE Function ( 53:20 min)

24. VLOOKUP: Two Way Lookup with VLOOKUP and MATCH ( 58:36 min)

25. VLOOKUP: Variable Tax Rate, 3 VLOOKUPS in one formula ( 1:00:18 min)

26. VLOOKUP and Table feature for Dynamic ranges ( 1:05:17 min)

VLOOKUP Shark WEEK

How To Use Excel VLOOKUP Function

Learn Excel VLOOKUP Function

VLOOKUP made easy

Excel VLOOKUP

Excel VLOOKUP Function

Brett BДень назадA little too fast in explaining.

shubham aggarwal2 дня назадAbsolute genius...

Vincent Lau10 дней назадThe author said "this is going to be an epic vlookup tutorial at the start". It is, I concur! Thank you so much. This is a masterpiece of vlookup tutorial.

ExcelIsFun9 дней назадGlad you like the video!!! Thanks for the support with your comment, Thumbs Up and Sub : )

kabir basu11 дней назадVery helpful video. Thanks.

Papa Pidos19 дней назадEXCELLENT! HATS OFF TO U SIR. THANKS A LOT!! GOD BLESS

Ellen-Jane Brooks27 дней назадThank you for the amazing video. I have dyslexia and I've always struggled with vlookup, your video is so easy to follow. Just wondering if you could help me further please.

I need to make a report that draws data from a shared drive. Once all data found, I need to make a graph from this data. (example) bottom half project forecast, Middle actuals, top why are we over or under.

Can you help me with this or direct me in the right direction please.

shashikiran R28 дней назадI'm Using WS spreadsheet instead of excel ,in the dynamic part i am not getting the result with the formula. please guide me ?

nikitha niki29 дней назадSir yourVlookup class is excellent, and your teaching style made me sit for a long time...my learning went on very interesting. Thank you so much for your efforts at free of cost for us...I am lucky I attended your class

ExcelIsFun29 дней назадYou are welcome! You can pay back the favor with a Thumbs Up and a Sub : ) Thanks for your support and comment!!

Doaa SamirМесяц назадGreat video & very supportive ,,,, Thanks!

Behlul antriМесяц назадHi, I have a problem to solve can u help? its in excel woorksheet

Quality Repair2 месяца назадGREAT JOB. VLOOKUP SIMPLIFIED, IN A CLEAR AND CONCISE VIDEO. GREAT JOB.

jacky chawla2 месяца назадIntroduced vba in excel sir ......mike

Steve Cancel2 месяца назадHi Mike, I can't use the F4 keyboard shortcut with a Mac.... other than manually typing in the $ signs, is there an alternative if Im using excel for Mac?

nora laboy2 месяца назадI have watched 2 hours worth of people try to teach this formula and have not been able to understand. Your easy explanation on how our thought process is exactly the excel thinking has allowed me to finally learn how to use it. Thank you!

ExcelIsFun2 месяца назадYou are welcome! I am glad you finally know VLOOKUP! Thanks for the support with your comment, Thumbs Up and Sub : )

Vishal Kumar3 месяца назадThank you for sharing Sir

ExcelIsFun2 месяца назадYou are welcome! Thanks for the support with your comment, Thumbs Up and Sub : )

Anita Burke3 месяца назад4:25 I've always noticed that people get the most confused by forgetting that the lookup value MUST be in column 1. I have also messed myself up time and again, by forgetting to fill down when I have a formula column, which is almost always. I either get a #N/A and cant figure out why, or worse yet, you won't notice if your average etc is off because you didn't fill down your formula

alexander akinkugbe3 месяца назадBest video on here for Vlookups. Thanks for this great video

ExcelIsFun3 месяца назадYou are welcome!! Thank you for the support with your comment, Thumbs Up and Sub : )

jean paul banza3 месяца назадThank you! for sharing your knowledge :-)

Colleen Kelly3 месяца назадThank you so much for this video!

Simple Shreedhar4 месяца назад@31:40 Retrieving 4 Records From table,

In that If we want to fetch only Last name and Phone Number Using I'd How to Do this??

Last name in column 2 and phone number in 5

by following above method I am not getting required Result..!!

In the Phone Number column I am getting First name.

Please help me to solve this

Thank you

Simple Shreedhar3 месяца назадThank you

dimitri3004 месяца назад^{+1}Easy! You can use the function MATCH that the professor is explaining a couple of minutes later.

You paste the following formula at Cell B18 : =vlookup($A$18,$A$4:$E$10, MATCH(B17,$A$3:$E$3,0),0).

Then you drag the formula to the next Cell(C18), where you have the Phone Number column ('Phone' in C17 ), as you requested.

On C18 the formula will be : =vlookup($A$18,$A$4:$E$10, MATCH(C17,$A$3:$E$3,0),0).

The MATCH function *matches* the Column name (First, Last, Phone etc.) with the same Column name from the big table that has all data.

jumyshihab4 месяца назадThanks a lot

Leticia Lopez-Hernandez4 месяца назадI have an excel file with 4 worksheets. One is the master list and the other 3 are department employee lists. Each sheet has the employee number and every department supervisor will be entering the date when an employee will be layoff. Is there and easy way to transfer this data from the department work sheets into master worksheet?Column A is the employee ID, row one is week beginning date. Managers entered a number one to mark the cell employee will be layoff.Can you help?

bini4 месяца назадSo what if you have a table that is always changing and you need to pull only certain lines of data to another sheet within the same file? So I have a table that has accepted contracts for our company. This table is always changing because jobs get scheduled and new jobs are added. I also have 3 people whose jobs are associated with. I need to make a list of only that person's jobs on a separate sheet. Can I do this with this function?

Mostafa Adel4 месяца назадThis is the real edutainment. Really enjoyed every single second. If your videos were to display in cinema, you'd win the Oscar everyday. May Allah increase you in knowledge.

Michael Ochinero4 месяца назадIs there a way to VLOOKUP from sheet to sheet? I I may have missed it in the video.

eng.mohammed nazly4 месяца назадTo learn how to use MS Excel for beginners and advanced we introduce you this channel

ruclip.com/channel/UCTXqwxNCcEHUbYjr76G50BA

Usurlia Benjamin4 месяца назадThis is great!!

Divya khare4 месяца назадIt's simply brilliant. One must go through every bit of it for maximum benefit. Thanks for this amazing video.

ExcelIsFun4 месяца назадYou are welcome!

Eric Ono5 месяцев назадHey! I have some doubts in the "Full record" example.

1) Why it does not work if i use "TRUE" as "range_lookup argument" in the "Numbers above column header" function? It should work as well, as we are using data validation, does not it?

2) Once I heard from one of your videos that the array must have the values in ascending order so that the lookup function can work properly, and that is not the case. How one can explain that?

Best regards from Brazil, and thank you so much for this awesome open lessons!

FrancisTram5 месяцев назадNice video course, thanks for sharing :-) !

Zoya Sharma5 месяцев назадThank you so much. The video is awesome! Thanks a lot. God Bless !!!

krishna mohan5 месяцев назадiwant H lookup

Leonardich305 месяцев назадhow to vlookup if my vlookup data is in the last column and I need to retrieve the data from 1st, 2nd columns for example?

how to do vlookup on cells with formulas in it?

good videos to make.

Ricardo Santos6 месяцев назадThank you a lot for your videos for sure the best explanations!

Can you tell me or redirect me to a Vlookup video that teaches how to consider an array of numbers (in this case are G/L Accounts) and returns the total sum (that are in another sheet) of the these accounts? It would help me a lot.

Thanks in advance.

pdo-samar hem86 месяцев назадExcellent Video on Vlookup functions.

ExcelIsFun6 месяцев назадGlad you like it! : )

Isabella L6 месяцев назадVery powerful and helpful video! Thank you so much.

ExcelIsFun6 месяцев назадYou are welcome so much!

Cyndi Barber6 месяцев назадI've gone over the vlookup using the named ranges and noticed, first of all, that my computer uses FN F3 instead of just F3. Next, I can't seem to use the named ranges without getting the N/A error. It looks right, but I can't figure out what's wrong =VLOOKUP(B27,vtable,4,FALSE)

Sudhir Bhardwaj6 месяцев назад^{+1}If someone asks me 'What should I do to learn Excel to an expert's level ?'.................

I suggest 'Excel is Fun is the best RUclip Channel of all time'

Sometimes it feels like as if you have created Excel

Hats Off man.....you are really a great teacher

ExcelIsFun6 месяцев назадThank you for your very kind words!!! And thank you for your support with comment, Sub and Thumbs Up : )

Echo Chen6 месяцев назадThanks so much, it is very helpful, good teaching, esp with workbook provided to practice. I will learn all thing about excel from you. Thank you very very much. I watched many videos, but yours is the most helpful one for me.....

ExcelIsFun6 месяцев назадI am glad that they are helpful! That is why I post them all. Thanks you for your Sub and Thumbs Up and comment on each video as support : )

Oliver France6 месяцев назад^{+1}Using this in 2017, Thanks bro

Oliver France6 месяцев назадCool bro thanks. Quick question. @52:57 why does the vlookup formula bring back a return as text when running the trim function?

ExcelIsFun6 месяцев назадI don't have cripto, I only have Pay Pal Donate:

ruclip.com/user/ExcelIsFunabout

Thank you!!!!!!

Oliver France6 месяцев назадI also got a job using this by the way...... Do you have a Bitcoin or another crypto currency address I am able to donate to ?

ExcelIsFun6 месяцев назад^{+1}You are welcome! Thanks for your support with comment, Sub and Thumbs Up : )

Darice Taipalus6 месяцев назадjam packed with info. I thought I knew Excel, bwhahahahha

ExcelIsFun6 месяцев назадGlad you liked the info : )

Mateusz K.6 месяцев назад31:27 I wonder why for an expandlable range in COLUMNS function you used =COLUMNS($B18:B18)+1 instead of =COLUMNS($A18:B18) - this would give you value we want - 2, without adding anything to the formula.

Dilshod7 месяцев назадExcellent

ExcelIsFun6 месяцев назадEXCELlent!!!!

Adiba Muminova7 месяцев назадThank you for the invaluable video!

ExcelIsFun7 месяцев назадYou are welcome! Thanks for the Sub and Thumbs Up : )

shahnawaz shaikh7 месяцев назадVery nice video you have cleared all concept very well awesome video.

ExcelIsFun6 месяцев назадGlad it helped!

Trang Le7 месяцев назадI'm wondering why you used VLOOKUP(A17, B6:D10,3) at 22:54. I did the same and got N/A. I changed to (A17, A6:B10,4) and got the desired value.

Nicole Allen7 месяцев назадthank you for this!!!! I forgot to add the addresses to a monthly spreadsheet I create for over 8k members, I was able to add the address in less then 5 Mins!!!! great stuff !!! watched about 5 minutes of the video because im very impatient lol but it was a very useful 5 mins lol keep up the amazing work!

Michael Zeng7 месяцев назад58:36. why include A7??

Sunil Kumar7 месяцев назадhi ,

do we have a video tutorial on how to use vlookup function when the data is filtered. .

i need the one

ABlue Faic7 месяцев назадreally learned a lot. Thank you so much!!

ExcelIsFun6 месяцев назадYou are welcome so much!

Naresh Fernando8 месяцев назадThank you so very much for this tutorial. Exactly what I needed to know. Cheers!

ExcelIsFun8 месяцев назадYou are welcome! Thanks for the Sub and Thumbs Up : )

J.C. Nizeyimana8 месяцев назадSincerely appreciate it.

ExcelIsFun8 месяцев назадThanks for your support with Sub and likes : )

killax10008 месяцев назадThese videos are invaluable. I've gone from office lackey temp to office Excel guru watching your videos. Came to figure out how to use VLOOKUP to find a way to search multiple parameters and the answer is make them into 1 parameter! Incredible. =VLOOKUP(critera1&2&3etc,CHOOSE({1,2},column1&2&3etc,resultcolumn),2,0)

Betul Champlin8 месяцев назадHi, can you please help me find the workbook? I can not find it in the link below for a reason. I appreciate the help.Download workbook: people.highline.edu/mgirvin/ExcelIsFun.htm

ExcelIsFun8 месяцев назадYes! I have added the direct links to the files below the video!!!! Thanks for your Sub and Like on each video : )

Amal Malenki8 месяцев назадYour videos are simply awesome and so simple to understand and navigate through. Thankyou Sir!

ExcelIsFun8 месяцев назадYou are welcome! Thanks for your Sub and Likes : )

PURVANG PANCHAL8 месяцев назадHey this is one very good tutorial i have seen in youtube

Sylvester Smith8 месяцев назадYou are an Excel god!!!!

Simple & Rational8 месяцев назадNever heard about the CHOOSE formula before. Practical examples with clear explanation. Great job!

Jennifer Hill9 месяцев назадI landed a great job and one of the key things I needed to learn was V-lookup. I had excel opened up, while I worked my way through your course. Thank you for helping me land a great job and I will keep watching as long as you are here to stay. You're the BEST!!!

Jennifer Hill8 месяцев назадQuestion, when I click the link to work on them, should I follow the video as well? I want to watch the video and work on the spreadsheet as well. Please provide more information.

ExcelIsFun9 месяцев назад^{+1}Congratulations on the new job! Yes, I am here to saty. I will keep posting! Thanks for the Sub and Thumbs Up on each video : )

Aminah Butt9 месяцев назадi luv watching ur videos..... so much to learn from them....thank you

Dani Emm9 месяцев назадThis was SO helpful!!! Thank you!!

ExcelIsFun9 месяцев назадYou are welcome! Thanks for the Sub and Thumbs Up : )

yudhishther sharma9 месяцев назадcan a formula give me following result

i have a unique number R1, check it in a coloumn if R1 is in that coloumn then a corresponding value, if no then check it in a coloumn of another sheet2 and give me corresponding value, if no then check it in col of sheet3....

shivam mittal9 месяцев назадLast one is not working in the Excel 2013 or am i doing it wrong?

Shelley Maxwell Channel10 месяцев назадMy dream is to take all your classes, I hope they can be available online. You are such a good teacher!

ExcelIsFun9 месяцев назадAll my classes are for free at RUclip. You can look through the playlist section and choose which one you would like. or tell me what class you would like to study and I can post the link here. What class you would like to study: Excel Basics? Intermediate and Advanced Excel, Office? Business Math? Statistics? Finance?

gwa5610 месяцев назадBest video explanation I've seen on RUclip thus far! Thank you.

Bitcoins10 месяцев назадSir, your video tutorials are very amazing! I'm currently making a medicine inventory worksheet. I'm trying to figure out how to update my inventory everyday from my dispensed medicines. Most of the medicines have the same names but different serial numbers. I need to know which medicines will be expiring before 3 months from expiration, almost finished stocks, auto updated medicine list with day-to-day numbers to how many being dispensed during that day. Thanks sir!

pauline scharf10 месяцев назадI am sooo happy that i came across this video !!!!T-H-A-N-K-Y-O-U !!!! This is the best you tube video ,

ExcelIsFun10 месяцев назадGlad the video helps! Thanks for the Sub and Thumbs Up : )

Patrice Thomas10 месяцев назадThank you very much buddy. Thank you for your honesty, making it easy to locate the excel file on your web. I very much appreciate that and for taking the time to teach it.

ExcelIsFun10 месяцев назадYou are welcome! Thanks for the Sub and Thumbs Up : )

Leon Chan10 месяцев назад^{+1}Being proficient in Excel is not difficult. However, teaching it the way you do, that is a gift. Your way of explaining and demonstrating these concepts are the best I've come across. I never thought I'd say this, but learning Excel is actually fun. Very big thank you!

ExcelIsFun10 месяцев назадVery Big: You are welcome!!!

Jeff Bennett10 месяцев назадI'm very new to VLOOKUP/Pivot tables and have to learn both for work. I am also pretty amaneur with Excel. Is Excel 2013 and now Excel 2016 fundamentally different in its features, or is this video still 100% relevant? Thanks

Jeff Bennett10 месяцев назадThanks for the response!

ExcelIsFun10 месяцев назад^{+1}Thanks for the Sub and Thumbs Up : )

ExcelIsFun10 месяцев назад^{+1}Excel 2013 and 2016 are very similar. The main differences are the upper end Big Data Analysis features, which do not affect VLOOKUP and Standard PivotTables features. Especially the VLOOKUP, where there is zero difference. This video made back in 2012 is 100% relevant.

Joey Sunshine10 месяцев назадMan this video is giving me life! Thank you a million and one times!

Joey Sunshine10 месяцев назад^{+1}Least I could do. Cheers!!

ExcelIsFun10 месяцев назад^{+1}You are welcome a million and one times! Thanks for the Sub and Thumbs Up : )

Sardar Abid11 месяцев назадi have big problem to used excel formules

hrmon pagrigs11 месяцев назадThanks a lot Sir, Its very comprehensive. Indeed a big help :)

ExcelIsFun11 месяцев назадYou are welcome!

nawal abdullahi11 месяцев назадwas helpful in my computer science hw, thanks:)B)

ExcelIsFun11 месяцев назадYou are welcome!

Mohamed haj hasan11 месяцев назадThanks for this awesome explanation for the Vlookup functionality, it was helpful appreciate your effort.

ExcelIsFun11 месяцев назадYou are welcome!

jacky chawla11 месяцев назадwill u please make a video on custom formatting&array advance

jacky chawla11 месяцев назад^{+1}one of the most powerful concept by anyone on youtube invincible !!!!!!!!!!

ExcelIsFun11 месяцев назад^{+1}Thank you for the kind words : )

Mili Biswas11 месяцев назадNow I can say, i know this function confidently in my dream job interview! thanks!

ExcelIsFun11 месяцев назадAwesome! : ) Good luck with the interview!

Cathe GutierrezГод назадThis is awesome. I almost could not find you videos again

ExcelIsFun11 месяцев назадI am glad that you found it again!

gassiusdaveГод назадHi ExcellsFun,

You are an Excel GOD!!!! Thank you for your help!!

ExcelIsFun11 месяцев назадNot a god, just a guy having fun with Excel : )

SikarateГод назадHello, I wonder about "20.Partial Text Lookup: RIGHT Function", if I don't know the last "num_chars" for each ID. How can I find the way to solve this matter?

For example Bellen-234-B25R, Carlota-345-C20R, Quad-765-Q20L >> the last num_chars = 4 digits, we can use =RIGHT(text,4) --to fix 4 in the formula, but if some ID may "Quad-765-Q20LK" >> the last num_chars is 5 digits. What is the formula we can use in this situation?

Thanks in advance.

Kalpana UГод назадThank you so much for the video,its really very helpful

ExcelIsFun11 месяцев назадYou are welcome!

Milan DaveГод назадAmazing explanation. Very useful :)

ExcelIsFunГод назадGlad it helped!

peacewize69Год назадAwesome video and I'm very glad you added the dynamic range at the end, that was one of my main questions was how to continuously update a table

J11 18Год назадAt 33:06, I'm a bit confused on how the ROWS function actually calls the specific data in this example. The plus 1 makes sense since it can call the next requirement since it's in order with the array we are getting the data from. But how does setting ROWS to (B$22:B22) actually call upon the needed data?

Online Tech SecretГод назадThanks for making this great video............

Maureen HillГод назадThis by far was the easiest tutorial for VLookup which I've struggled with for years LOL. Thank you for this great video! Happy Holidays!

ExcelIsFunГод назадYou are welcome!

Travis.H WongГод назадHow can I vlookup the answer without know the source is coming from which table?

Rannie RigodonГод назадBravo sir.. advance Merry Christmas and Happy New Year.

ExcelIsFunГод назадHappy Holidays to you too : )

Geana GutierrezГод назадThank you!!! What a great class, I have learnt a lot!!! This is reminding me when I did visual basic with excel. I did understand everything, but I did have some questions about the choose formula. When you have to use {} or tables, I would like to know the logic behind the formula and in which more scenarios you can use it. Thank you.

ndelpurgГод назадMANY THANKS FOR YOUR GREAT WORK !

ExcelIsFunГод назадYou are welcome!

DJ Al-shaikhГод назадCan you briefly explain what does a "Case study excel base (vlookup)" means? uncle google did not help :(

DJ Al-shaikhГод назад=VLOOKUP(B100,CHOOSE(VLOOKUP(A100,$A$108:$B$110,2,TRUE),$F$101:$G$103,$F$107:$G$111,$F$114:$G$116),2,TRUE)

ExcelIsFunГод назадI talked about this early in the video. TRUE (Approximate Match) is the default. If you only put three arguments, it does Approximate Match by default. Be sure it is like: VLOOKUP(A100,$A$108:$B$110,2) and not VLOOKUP(A100,$A$108:$B$110,2,)

DJ Al-shaikhГод назадin the VLOOKUP with 3 LOOKUP tables why havent u used TRUE so that u can get a percent of the nearest number.

ExcelIsFunГод назадI talked about this early in the video. TRUE (Approximate Match) is the default. If you only put three arguments, it does Approximate Match by default. Be sure it is like: VLOOKUP(A100,$A$108:$B$110,2) and not VLOOKUP(A100,$A$108:$B$110,2,)

DJ Al-shaikhГод назадthanks so much for the very useful tutorial.. i am having my case study test for job interview and they told me it is based on vlookup function.. hope do well... may I ask you if you can provide us with the sample spreadsheet you used in your tutorial? Cheers

ExcelIsFunГод назадYes, click link below video and go to VLOOKUP Week section.

riyam ibrahimГод назадthank you so much.

ExcelIsFunГод назадYou are welcome!

fmanarutoГод назадI love you bro

ExcelIsFunГод назадGlad the videos help!

Deepak KumarГод назадIt's amazing for the Beginners, Thank u sir

ExcelIsFunГод назадYou are welcome!

FPrimeHDГод назад^{+1}You make me want to go back to college and take a class again.

ExcelIsFunГод назад^{+1}Glad you like it!