Видео


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



Download workbook: http://people.highline.edu/mgirvin/ExcelIsFun.htm
This video is a part of VLookup Week: http://vlookupweek.wordpress.com

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


Просмотров: 1595302
Добавлено:
Длительность: 1:8:3
Комментарии: 1435

Тэги для этого Видео:



Найти больше видео в категории: "27"
Видео загрузил:
Показать больше видео, загруженных


Похожие видео:

Excel for Accounting: Formulas, VLOOKUP & INDEX, PivotTables, Recorded Macros, Charts, Keyboards

Просмотров:
Excel Basics #23: VLOOKUP function formula

Просмотров:
PivotTables Are Easy! 30 Examples for Highline College Professional Development Day 2014

Просмотров:
What's New In Excel 2013: Flash Fill, Functions, Data Model, PowerPivot, New Charts, Table Slicers..

Просмотров:
How to Extract Data from a Spreadsheet using VLOOKUP, MATCH and INDEX

Просмотров:
Excel 2010 Tutorial: A Comprehensive Guide to Excel for Anyone

Просмотров:
Excel: Tips/Tricks For Large Amounts of Data

Просмотров:
Vlookup Excel Best

Просмотров:
How to Combine 2 Excel Workbooks Using VLOOKUP

Просмотров:
VLOOKUP Introduction, Explanation, Example in Excel (1 of 2)

Просмотров:
How to Use VLOOKUP in Microsoft Excel 2010

Просмотров:
Create a searchable drop down list in Excel

Просмотров:
Excel Lookup Series #1: VLOOKUP Function 1st Example

Просмотров:
Introduction to Pivot Tables, Charts, and Dashboards in Excel (Part 1)

Просмотров:
Compare Two Excel Lists to Spot the Differences

Просмотров:
VLOOKUP Between Separate Workbooks and Worksheets in Excel

Просмотров:
Highline Excel 2013 Class Video #35: Excel 2013 PivotTables: Basic To Advanced 58 Examples

Просмотров:
Improve your efficiency in Excel -- VLOOKUP and Pivot Tables by 7city.com

Просмотров:
Build a KPI Dashboard in 5 minutes

Просмотров:
How to do a VLookup in Excel 2007

Просмотров:

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

Автор hbbbxxx ( назад)
seems like u live near the airport. could hear airplanes in ur video. :)

Автор Laurentiu Neagu ( назад)
Very good tutorial with practical examples , thank you man ! ,

Автор hbbbxxx ( назад)
simply amazing... you are clear and to the point... love it... thanks

Автор El chaella ( назад)
great tutorial. thanks

Автор Chita onRun ( назад)
Best tutorial for VLookup on the internet.

Автор Don JO ( назад)
This was incredibly valuable to me. The content and the instruction is
excellent. In 30 minutes you taught me how to do VLOOKUP! and in a way that
made it very easy to understand. I look forward to learning more from you.
Thanks much!

Автор joseph lennon ( назад)
Thank you from a complete novice in Ireland who just finished a college
assignment in excel thanks to this video. 😃😃😃😃

Автор Mohamed Osman ( назад)
You're a real MASTER ....Thanks a million

Автор Angelo Young ( назад)
amazing video...

Автор John Shervell ( назад)
wonderful but I have 2 lists one short 10 names and one long 50 names
.using vlookup to find a name from the long list in the short list ,works
ok but if it does not find the name it shows an error...I wish it to leave
the value in a cell if it does not find the name.how do I do that please

Автор adrian andrei briciu ( назад)
Excellent good explications. Thanks

Автор yabadabadu pica ( назад)
Hi very good your video thank you

Автор Charmaine Thomas (669 лет назад)
wow this was awesome thank you very much

Автор ramya suresh ( назад)
Your way of teaching is very good, I can improve my excel skills and apply
for the job once again. Thank you! so much for sharing your knowledge.
What is the file name to dowload?

Автор Pedja Simic ( назад)
Da te pitam ja......jel jebes ti nesto?

Автор Muhammad Abbas Arif ( назад)
Thanks, this an amazing video. Posting such videos is part of community
development.

Автор Mike Nathan ( назад)
Again, thanks for posting! I had a question that I am still unsure about
and I dont think it covered or your other videos. I know you are busy but
if there is any assistance you can give me, I'd really appreciate it!

I have to enter/redeem spiffs on a monthly basis so I currently compare
model #'s that I sold to what program is available for me to redeem.
However the problem is that the models I need to compare to are not always
listed separately therefore I cannot export to excel and use VLOOKUP (since
its not on excel ,its on a website) . If you see below, its because some
lines have multiple models separated by a comma, if I copy and paste to
excel, VLOOKUP would not work properly.

EH32BR81QB $75.00
EH24BR81QS, EH24BR10QS $50.00
EH30EC45KB, EH30EC45KS, EH36EC45KB, $40.00

Is there a fix to make them all get separated into a different row like
right below?
EH32BR81QB $75.00
EH24BR81QS $50.00
EH24BR10QS $50.00


Again, any help you could offer would be so great. I have excel 2007
currently. Thanks again!

Автор Amanda L ( назад)
What if you want to find all of the dates in the spreadsheet that are 90
days or more past due. How would you do that in Excel?

Автор Andrew Bradford ( назад)
Good video man. Lots of options when someone is trying to learn the vlookup
function.

Автор Chulaka Madurapperuma ( назад)
its 2 am I'm total out of kind how to do vlookup tnx a lot u save ma day

Автор roderick mose ( назад)
Hi Guys, in the example on the 'LookUp Last' worksheet @ time 29:21 Last
Trade Entered on Specific Date, instead of using divide by one in the
formula, shouldn't multiple by one also work as it would convert all the
trues and false into ones and zeros only? i tried it but only works to some
extent!

Автор Nishad Hussain ( назад)
thanks a loooooooooooooooooooooooot man

Автор Tux ( назад)
Very clear. Even better than my private tutor Thank you so much!

Автор Narcissus DP ( назад)
https://www.youtube.com/watch?v=08S1MC8XQsk

Автор Cassondra Dixon ( назад)
Very Helpful. I love how concise he is and he covers various vlookups.
Highly recommend, Excellent learn

Автор Siddege Ahmed ( назад)
Thanks man! Landed a job by studying from these videos.

Автор Zaf Iqbal ( назад)
Brilliant video........I looked at VLOOKUP and thought it was a different
language but in the first 5 minutes I can't believe how easy it is !!!
Thanks you and well done on fantastic video!

Автор rucker369 ( назад)
Does anyone know how to explain what locking means, or know a video I can
watch? I can't go any further than 31:27 till I know what locking mean

Автор khim seily ( назад)
Why search function is put 7? And why minus 1?

Автор Diana Michalevic ( назад)
Amazing :) Thank you very very very much :)

Автор Diosdado Miolata ( назад)
superb!!!!

Автор Harish Krishnamurthy ( назад)
awesome!

Автор tarine felix ( назад)
@ExcelIsFun Really great tutorial! I have a question though.. can you put a
vlook up for a whole category? example : there are 20 data, there are 4
categories (so 5 data have similar information), I want to look up one
category, is there a function for that?

Автор Kithsiri Advertising ( назад)
Thank U Very much. I learn Lot Of things In This Video .Thank U again . TC

Автор Tony Perez ( назад)
This is a great way to learn and advance in excel, thank you for making it
easy to understand.

Автор Amanda Villaparedes ( назад)
I have to bump up my excel skills for job applications since they test as
part of the interview process. Your videos are amazing :)

Автор Karla Duenas ( назад)
You are the best!

Автор jessie johnson ( назад)
This was so helpful to me! My college classes never provide lechures and
I'm preparing to take the MOS Excel Expert exams. i needed a source and
this was truly perfect!!

Автор Bruna Jermann (441 год назад)
Amazing video! Thanks a lot!!

Автор Tiffani Scott ( назад)
Thank you so much this is wonderful! 37:00 is finally what I e been
searching for. I'm keeping this video very good

Автор Muhammad Naveed ( назад)
REALLY EXCELLENT

Автор md sahubar ( назад)
excellent tutorial. keep it up

Автор Callum Bannister ( назад)
I sometimes find it really difficult to explain what I want Excel to do, so
I could never really explain the issue.. This solves one of them perfectly
and is pretty simple!

Such a great resource, thank you for your time and effort! Much
appreciated!

Автор Cosmas Ezeuko ( назад)
Good job ExcellsFun. Excellent
With this kind of video free-of-charge, ignorance is a choice!!!

Автор gayatri mahapatro ( назад)
thank you sir

Автор Mei Terumī Mizukage (1056 лет назад)
I am really thank full for all the helpful tutorial.... You the best... ^_^

Автор only1barbi ( назад)
I watched 5 other videos on VLookup before finding yours. Obviously, the
first 5 didn't explain it very well or I wouldn't be here...THANK YOU FOR
SIMPLIFYING THIS FOR ME AND PROBABLY SAVING MY JOB. TY TY TY

Автор Devesh Somani ( назад)
Sir! You are God Sent! Cannot thank you enough for what you do! Thank You!
Thank You! Thank You!

Автор Yannick Morgan ( назад)
So you have to download the workbook sheet in order to do a vlookup through
the workbook pages. does anyone know where I can go for that? This is
regarding number 11

Автор Md Tofiqul Islam ( назад)
আমার জীবনকে বদলে দিছে! (Excel Magic Trick)

Автор Stephen Weiss ( назад)
I tried the vlookup with choice of 2 columns (17 in the table of contents)
in a very large worksheet. The formula only worked for the first two
rows. The remaining 735 rows produced "n/a" error messages. I wound up
creating a helper column (16 of the table of contents) but would have
preferred the other formula to keep my worksheet clean. I used the $ sign
in the arrays and then tried to remove them but nothing worked. F9 to see
the formula did not work because the size exceeded the limits. Any ideas
on what else I can try to make the choose function work?

Автор MrFriedgreentomatos ( назад)
I've just got one, No two comments, the first Thanks and the second ;
"Boom" - cheers, an excellent video

Автор harald808 (2009 лет назад)
you went to fast on this tutorial

Автор harald808 ( назад)
i dint get it its seems hard or may be you dont know how to explain to the
begginer

Автор Justin Peters ( назад)
It's incredible to see someone who is so savvy with excel! Practice makes
perfect! Good stuff man! Keep them coming. If you have any room left from
your THOUSANDS of useful and beneficial videos!

Автор justin wang ( назад)
it is very useful for me ,thank you

Автор Mahfuzul Haque ( назад)
good

Автор jaena paula balbedina ( назад)
Hello sir! Your Excel-lency, Mr. Mike Girvin! I'm a big fan of your work!
You have a knack of making complicated stuff look easy peasy... It's me
again. I don't know if you're viewing the comments since I understand this
is waaay back and I did not receive a reply on my previous question. I just
need to ask though with regards to PARTIAL TEXT LOOKUP, how do you make
excel search/look up a name from a database by only the first three letter
of the last name from a random list? In other words, how do I make Excel
find the first 3 letters of any name from the list if I don't exactly know
what to put in " " when I use SEARCH?

Автор Osvaldo Aponte (204 года назад)
This is awesome! Thank you!

Автор jaena paula balbedina (1744 года назад)
This is great! Thank you for sharing your extensive knowledge on Excel! You
have opened my mind to a lot of possibilities and new ideas on what we
could do with this powerful tool! I have a question though. I'M DOING A
SORT OF TIME AND MOTION STUDY WHERE I NEED TO MEASURE THE NUMBER OF MINUTES
IT TAKES TO DO A SERIES OF TASKS AGAINST THE NUMBER OF MINUTES IT TOOK TO
WAIT IN BETWEEN THE TASKS (WAITING TIME VS. PROCESSING TIME), AND I'M USING
THE FORMULA: Example: =MINUTE(B2-B1). THE FORMULA IS SUPPOSED TO SUBTRACT
THE EARLIER TIME FROM THE LATER TIME. HOWEVER, MOST OF THE TIME IT'S NOT
ACCURATE IF THE DIFFERENCE IS TOO BIG (MORE THAN 60 MINS). ALSO, IF IT'S
HOURS APART IT DOESN'T COUNT THE HOURS BETWEEN POINT A AND POINT B. IT
SEEMS EXCEL IS LIMITED WHEN COMPUTING MINUTES IF IT'S MORE THAN AN HOUR. DO
U HAVE ANY WORKAROUNDS?

Автор Maciej Berlinski ( назад)
Thank you @ExcelIsFun for this one. On the basis of this I created a mobile
number generator for 4 different source number types and 3 different
outcome number types. All in one cell!

Keep doing this great knowledge sharing as this is amazing!

Автор Jaya Francis ( назад)
Hi Your videos are really informative and clear and I've benefitted very
much from it. I'm really a million $ thankful to you for what you upload in
your channel. I've something to ask you. I've a situation where in a table;
same look up value ( Eg: Carlota, Carlota Carlota) repeated three times and
corresponding value in next column is different for each Carlota. For an
argument This table has 2 columns only. I look up Carlota but obviously
Look up function always gives "N/A" in cells. Would you please suggest if
there's any function that can tell me if there's a function that can help
me to find all the different values that a Carlota stands for in a table or
a trick to tackle this situation. Can you also suggest if there's a
function that will tell me how many times a Carlota is repeated in a table
because I fed up using with Ctrl+F.

Автор Marlene Pisani ( назад)
I have done this and it does work but i have a table with over 20,000 items
and some items might contain additional information that i dont always
know, how can i create a formula that finds the first part of the item #, ,
most of my items are 8 digits long but then also have a space then other
numbers,, example (900134142 120/60) ,or (900134142 24-60), is there a way
or a special formula that will find these first numbers without having to
know the last ones.?

Автор Fernando García ( назад)
Thanks :) !

Автор Ben Rawner ( назад)
This video was excellent. Focused easy to understand and enlightening. You
rock at teaching!!!

Автор A KP ( назад)
Excellent Tutorial! Loved it and learnt a lot of key uses. Thanks so much!

Автор Bernhard Mallette ( назад)
Great - very useful. Never done VLook ups before this was a great help

Автор Dean House ( назад)
Can i make a vlookup that will look for certain words in a sentence then
return a value? So out of a the Principal payment for load id 374858375,
can i search for "principal payment" and get it to return a value?

Автор Aparna Shetty ( назад)
Good quality teaching!!..now a days nothing is free. Appreciate your job
here!

Автор RealSacredCow (1257 лет назад)
I have enjoyed these videos a lot over the years, and still learn something
new with each video I watch. I have a question about something I have been
working on recently. I am in a league that uses different point awarding
scales based on where the person is in the brackets when facing an opponent.

Example 1:
Player A has 15 points
Player B has 10 points

When these players face each other, if Player B were to defeat Player A,
they would be awarded 3 points. Though, if Player A were to defeat Player
B, they would be awarded 2 points. Also, if by chance both Player A and
Player B were to draw in their match, Player B would receive 1.5 points,
and Player A would receive 1 point.

Example 2:
Player A has 9 points
Player B has 9 points

If either of these players were to win, they would receive 3 points. If
they were to draw, they would each receive 1 point.

Is there a way that this could be automated within Excel, using VLOOKUP, or
another function(s) combination?

Автор Adam Wallett ( назад)
Thanks so much for putting tthis together :)

Автор lpfanbase ( назад)
For two way lookup (1:00:00) .. why is the match column array from A7 to
F7? Shouldn't it just include B7:f7 ?

Автор Jerome Cabaluna ( назад)
How do you lookup if the lookup value is on the right side column and your
value array is on the left side?

Автор Bismark Adum-Kwapong ( назад)
Great teaching and learning videos. Thanks for sharing.

Автор Mohit Saini ( назад)
cant get the Work Book sir.. please help

Автор grammar police ( назад)
Orientating is not a word; the correct term is orienting.

Автор Prasad Jampana ( назад)
Great video...Thank you!

Автор UPVYPAAR.IN ( назад)
grt lokk

Автор Emilie Priyadarshini ( назад)
Hi +ExcelIsFun


I really like your YouTube videos series.

I am so curious to know about how much time and efforts did you put to make
this kind of videos?


Do you do this full time or do you do it when you have time on weekends?

How much time do you give daily to make these videos? do you make it at
home or you went to some professional studio?

What kind of tools do you use to record these videos?


Do you record and edit this video by ownself or do you hire some company to
do that?

What kind of benefits do you get because of these videos?



How much money did you spend on this and how much money do you generate
from YouTube monetisation?

Is it good idea to make video and give it for FREE rather then writing a
book?

share your tips to make this kind of exclusive youtube channel.

Who should make these kind of videos.?

Автор Rafael Pires ( назад)
Thank you for sharing your knowledge. I got a new job and one of my duties
is evaluate and develop reportings and I was pretty worried about
that...I´m glad I found your channel, it's helping me a lot.

Автор Abdi Shibis ( назад)
How come I can not use Ctrl+F3 another sheet to get NamedRange
DLBoomerangs? You didn't mention if this trick of Vlookup with Named Range
Trick can only be applied with the same Working Sheet or different sheets.
Also don't i need to use F4 when i select the Range or it don't matter? By
the way I am obsessed your website
http://www.mrexcel.com/learnexcel/tag/excelisfun/ and Youtube (Excellsfun)
and I am your Twitter @ExcelIsFun Follower.

Автор So Happy To Sew ( назад)
Ok, I have a question for you, and I'm not even sure how to write it out.
:) I'm trying to affinitize a list of ~1,500 project ideas (text field),
using a list of approximately 50 "buckets", which are also text. For
instance, one project idea might be something like, "Use 3D printing for
low volume parts", another idea might be something about an end cap, or a
latch, or whatever. My list of bucket items have things like: 3D printing,
end cap, latch, etc. How do I get the formula to return the appropriate
bucket item (3D printing, end cap, etc.) relative to what's in the text for
a project idea? Thanks!

Автор Nivek ( назад)
Thanks Man you will help me to get a Job 

Автор Rade Blanusa ( назад)
WOW! Great stuff bud. Big fan here <----

Автор JOE SKINZ ( назад)
This helped big time. Thanks for putting this togther

Автор coachgeo ( назад)
ok Vlookup might be good for my needs..... but.... there is a stick in the
mud that might make it not usable for me. Let's say I have a table of
data. Strung out on one row across the columns with about 30 bits of info;
each in it's own cell/column on that row. About 300 rows total in the
table. First cell of each row identifies say Boomarang color's that can
vary say blue, red, yellow etc. With V lookup I can pull all the data of
each of the blue boomrangs (of various types, brands etc.) to a new
location where I can deal with Just the blue one's out of the 300+ records
of all the boomarangs. Now when dealing with these Blue ones I will be
changing data in it's associated cell/columns; example cost in one column
and diameter in another, type of plastic used AND dates each of these
records were changed. Here is the stickler... on the fly or end of day?
how do I get this new updated info for all the blue ones back to where the
original/primary data table is kept? Is there a "V-Send it back to it's
home table and location" command :) ?

PS- reason Vlook up is potentially better than just "sorting" the original
table is I find the date's I get recorded in a cell via circular reference
formulas that record dates (Today command) when data changes, *somehow
these dates get changed to "the date the sort was done" instead of keeping
the date that was recorded on the original date the data was changed. I
need these dates for legal reasons. I'm not doing boomarangs obviously.
This is student progress records

* these date's getting changed was happening in OOCalc. Just recently got
Excel 2007 but concepts across the two are strikingly similar and was told
by others Excel would react the same way.

Hope this makes since.

Автор ExcelIsFun ( назад)
Harmanpreet Singh , try:
Highline Excel 2013 Class Video 19: Text Functions RIGHT, LEFT, MID, SEARCH
for Partial Text VLOOKUP
https://www.youtube.com/watch?v=vbu0PX8_hm0

Автор ExcelIsFun ( назад)
Harmanpreet Singh , try:
https://www.youtube.com/watch?v=vbu0PX8_hm0

Автор Harmanpreet Singh ( назад)
This was very helpful for me.
But I have Question,
In case of Partial text-
If Mid ID and Right ID is not consistent then what would be the formula for
above situation?

Автор Mutlu Gundiler (1919 лет назад)
One can't learn any more within one hour. Thanks much.

Автор Ahmed Ryan ( назад)
Man,that is absolutely amazing ..thanks man

Автор MILWAUKEE “JETSTREAM” MOTOR ( назад)
why does there have to be data in a15 for the formula to work
why cant I use a5

Автор MILWAUKEE “JETSTREAM” MOTOR ( назад)
if I enter a5 as the vlookup formula it doesn't work
why do I have to use a15 and not a5 in the formula please clarify?
I think I am missing a point

Автор workpays ( назад)
Thank you. Really.

Вставка видео:

URL 
Ссылка 

Поиск Видео

Top Видео

Top 100 >>>

Видео

Seo анализ сайта