Видео


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


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

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



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


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

Excel Basics #23: VLOOKUP function formula

Просмотров:
Excel for Accounting: Formulas, VLOOKUP & INDEX, PivotTables, Recorded Macros, Charts, Keyboards

Просмотров:
INDEX & MATCH functions Beginner to Advanced 19 Examples (Excel VLOOKUP WEEK Video #3)

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

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

Просмотров:
Using Excel 2010 - Full Tutorial on the Various Functions

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

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

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

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

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

Просмотров:
Excel Magic Trick 913: Select From Drop Down and Pull Data From Different Sheet

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

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

Просмотров:
Excel Magic Trick 783: Date Functions & Formulas (17 Examples)

Просмотров:
Excel Basics #20: Data Analysis Pivot Tables PivotTables

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

Просмотров:
Tour Of Excel: Formulas, Formatting, Sort, Filter, PivotTables, Charts, Keyboards

Просмотров:
Excel for Accounting and Finance - Northwest Accounting Educators Conference 2012 - NAE 2012

Просмотров:
Making an Excel Dashboard - Tutorial #1 Copying Widgets

Просмотров:

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

Автор UP VYPAAR.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

Автор Quin Harsh ( назад)
Fantastic :) great work

Автор 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

Автор Subra Manyam ( назад)


Автор Subra Manyam ( назад)


Автор 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 (1918 лет назад)
One can't learn any more within one hour. Thanks much.

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

Автор Archana Gajakosh ( назад)


Автор Latha Forever ( назад)


Автор Lilan Hemantha ( назад)


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

Автор MOTO STELLA ( назад)
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.

Автор Tracey Donoghue ( назад)
What a great tool! I've shared this with my team - I hope it saves me a
lot of future coaching. Thanks for putting this together!

Автор Brenda Martinez ( назад)


Автор Brenda Martinez ( назад)


Автор gawei young ( назад)
快来看看YouTube上的这个视频吧:

Автор Wong Liyong ( назад)
awesome video

Автор Monica Estrada ( назад)
Please help. My boss created a calendar in excel where she inserts staff
name under appropriate date and time she is to meet with them. Then she
has a separate spreadsheet with list of staff name, where she checks them
off when they have scheduled their monthly meeting/eval. So that instead
of going through the calendar she can go to the list and locate who has or
hasnt scheduled for the month yet. She either updates one or the other. Im
supposed to make sure that all staff meet with her on a monthly basis. So I
sit here trying to match the spreadsheets(list to calendar and vice versa)
and by process of elimination figure out who still hasnt had their monthly
meeting. Then I have to hunt them down. So... what can I create in excel
that would help me solve my problem? What formula can I use that would do
the matching up for me? Ive tried the formulas that you have shared but
its not working for me. Please help! I cant go another year spending
hours matching names...Desperate. Thank you!!!

Автор carrieyueng ( назад)


Автор Fayaz Abdul (582 года назад)


Автор 92shehan ( назад)
Hi. Is it no longer possible to download the work books?

Автор Body Nation USA ( назад)
What an awesome video! I wish I would have come across it a bit sooner
because I've already flunked my midterm.....

Автор Prashanth A ( назад)
OMG. All these days i am struggling with Vlookup because data format is not
unique everytime.This video explained me how to use lookup without
adjusting existing data.
I love this.

Автор garra gao ( назад)
Thank you, man!!!!!

Автор Tyler Schaner ( назад)
First off, great video! Extremely helpful.

As for my question; regarding duplicate values in the "lookup_value" the
vlookup will only register the first value it finds, is there a way to get
all values?

Example: my lookup_value are dates (3/6/2015) and my col_index_num are
dollar amounts and I'm trying to add all sales on 3/6/2015.

It currently is registering the first sale 3/6/2015 however the subsequent
sales are not being added to the grand total. Having 2 look up values
wouldn't work because there are duplicate sale totals (i.e. both
lookup_value's are 3/6/2015 & both col_index_num's are $160) They need to
be separated to denote separate transactions.

Thank you in advance!!

Автор Handoko Liam Rianto ( назад)
Hi, I just realized all this time I've been using the wrong lookup
function, I keep using LookUP() function for a vertical table everytime I
have new values I have to update the table in alphabetical order. However
with Vlookup I dont' have too. Any disadvantage of Vlookup I need to be
aware of? Thanks.

Автор Gareth Reagan ( назад)
Regarding using CHOOSE to work with multiple tables, if there are many
tables to choose from would it be more efficient to name the ranges of each
table then use INDIRECT in the VLOOKUP second argument to build the name of
the correct table and then return the array of data? e.g. Name data range
in the 3 tables tblABC, tblEDR and tblEDS, then use
VLOOKUP(B4,INDIRECT("tbl"&A4),2) in your example 17.

Автор ssp1r1t ( назад)
At the "Two Way Lookup" is it ok to use this formula
=INDEX(B8:F12;MATCH(B3;A8:A12);MATCH(B4;B7:F7))
Instead of the VLOOKUP

Автор delboytrotter77 ( назад)
The video was epic and I fully enjoyed it esp. the 'Trim' function as well
as the dynamic tables!! two thumbs up!

Автор Cheryl Clarke ( назад)
Thank you!!!!!! This is so clear and to the point...savior!

Автор Tony Valadez ( назад)
You helped me nail a job interview. Thank you!!!!

Автор Jamie Bates ( назад)
Great video. VERY, VERY helpful. Question (44.54 min) why did you use -1
in the SEARCH function?

Автор Joyousj ( назад)
OMG just stumbled on your video..THAAAAANK YOOOOU! THIS right here, just
saved my butt! Whew. Going to make my job a lot easier! Be blessed! 

Автор helen gatlin ( назад)
very good vlookup tutorial, and a long one. Spent some very valuable time
on this. Thank you very much for sharing this wonderful video and workbook
with us.

Автор Michael Min ( назад)
I was so lost with vlookup and other excel functions at work and you just
taught me all of those in an hour! AMAZING!
Thank you very much for sharing these valuable videos :)

Автор Yay Joe ( назад)
hey Mike, thanks for this. Can you show how to do the same trick with the
RIGHT function? In the last case you have assumed that the last part of the
string will always have only 3 characters. What if that too is varying? How
can we extract it?

as an example, suppose the data was:

Bellen-234-BB25R
Carlota-345-CC11120R
Quad-765-QQQ20LJK

Автор L'Ranj V ( назад)
you are awesome! Thank you!

Автор Sam Diboma ( назад)
Please how can I merge 12 excel sheets containing data of 50 countries
spread over 30 years using the VLOOKUP function (the data are contained in
one excel file)
Thanks

Автор Ali Hassan ( назад)
Can anybody directly send me the link from where I can get that workbook
downloaded. I'd greatly appreciate that. 

Автор Marie clark ( назад)


Автор Vatthana Batonunh ( назад)
Million thanks for useful trick

Автор Zaheer Abbas ( назад)
Thank You so much for such kind of quality training video. God Bless You.

Автор Michael Pasqua ( назад)
Seriously, this is the most helpful video I've ever watched.

Автор Luis Sánchez Cortés (892 года назад)
great video, I have a question: Is it possible to use the vlookup function
in different workbook with multiple sheets? I need to look up for some data
in a different workbook that contains multiple sheets, I would like to make
a search in each sheet

Автор Lewis Parsell ( назад)
This is great. My question is this. I have a large table as a parts list
and as things get added I sometimes need to great a new column somewhere
within table. This causes all cell references to the right to be off by one
number. Is there a way of having the VLOOKUP formula update as columns are
added?

Автор Raquel Catalan ( назад)
I have tried to pull the file and I could not find it.

Автор Theodore Blumberg ( назад)
Wow, that was really amazing. Especially example 25 and 26. Thanks. 

Автор Abdul Jaleel Uae ( назад)


Автор Aditya Kamath ( назад)
Thanks so much for sharing these videos!! They're awesome! :)

Автор Lillornaful ( назад)
Completely awesome tutorial - watched the video, downloaded the workbook
and worked through it, and checked back to video for reference (when I got
stuck) ... Totally Awesome highly recommended! Thanks ExcellsFun!

Автор JellyBeanInTheNight ( назад)
This just may be the best training video I've ever watched. Thanks!

Автор Learn Excel Formulas ( назад)
Awesome tutorial on Vlookup, thanks for putting this together!

Автор shiva prakash ( назад)
fqwefw

Автор Sherry Thompson ( назад)
I love these videos, but I can't seem to find the help I need with my
Vlookup. I have a list of accounts all starting with the same prefix, and
different suffixes (ie: 6000-100, 6000-215, 6000-350, etc.). I need to be
able to do a vlookup so that it returns the values if they contain the
prefix I'm looking for. I've done the vlookup using the prefix and the
wildcard, but if I repeat the formula down, it is just returning the first
value in the list (6000-100). How can I get it to return the values that
are listed with the desired prefix? I cannot separate the column so I have
to be able to read it as is. Any help/advice is GREATLY appreciated!!

Автор Paul Gwilliam ( назад)
Very good video thank you very much.

Автор Marcus Anthony Francisco ( назад)


Автор Ganesh Parab ( назад)
THIS IS REALLY AWESOME MAN...HATS OF TO U...............

Автор Jessica To (1278 лет назад)
I am interested learning about vlookup, Hlookup and pivot table. Do you
have any courses available and how can i get in contact with you?

Автор Tara Zimmerman ( назад)
I'm a career administrative assistant, and didn't know these functions. I
am so glad I was able to learn these. Your explanations were very clear and
your examples well done, step by step. Thank you!

Автор Pearly Sunitha Samuel ( назад)
Thank you so much. So much information for free ! Thanks for all your
efforts God bless you abundantly

Автор Israel Galvez ( назад)
i cant open the workbook =/ its not working, says error

Автор ann shameen ( назад)
Thank you so much for your effort to improve our knowledge.

Автор BATTLE BORN 360 ( назад)
Question - what if I am using VLOOKUP to get the cost of an item, but then
later on the cost of the item changes. Do I need to create a whole new
price column, or is there a way to change the item cost without affecting
previous data? Take a store front for example. I use VLOOKUP to populate
the cost of a "chair". Later on, the price of the chair goes up. If I
update the item cost field, any sales I recorded previously will then
update as well. Is there a way to use VLOOKUP, then lock the field so that
it won't update when I change the item price later on? Hope this makes
sense. Thanks for a great video!

Автор MrSarky1992 ( назад)
Hy! I hope you are reading this. So immediately when you were talking about
the Partial Text and MID function I started to wonder that "okay, but what
if that ID is not always 3". I swet blood but I came up with this solution:
- First: change the VLOOKUP table ID column to text with text to column.
- Second: use this monster:
=FKERES(KÖZÉP(A10;SZÖVEG.KERES("-";A10)+1;SZÖVEG.TALÁL("-";A10;SZÖVEG.KERES("-";A10)+1)
SZÖVEG.TALÁL("";A10)-1);D10:E13;2;0)

It's in hungarian, I believe that the exact version in english is:
=VLOOKUP(MID(A10;SEARCH("-";A10)+1;FIND("-";A10;SEARCH("-";A10)+1)FIND("
";A10)-1);D10:E13;2;0)
It's ugly as hell but believe it or not it's actually working!

Love ya!

Автор Una Sana ( назад)
where can i download this file.? I really can't find it!
Thanks and yeah great stuff here.

Автор Jin Aura ( назад)
Dude, you're awesome. Thanks so much for your video and the thorough
explanations!

Автор TheAdventurousPirate©™ ( назад)
Were do i find vlookup in excel? anyway, im going to give you a thumbs up
and sub for GREAT useful info!

Автор Chinese Hacker ( назад)
This was awesome!!

Автор Dashzeveg Bayarsaikhan ( назад)
Thank you so much. You are the best in all the videos tutorials I watched.

Автор Marcus Anthony Francisco ( назад)
Vlookup Training Advance

Автор Shahriar Arif ( назад)
7.59 , What if I want to look value using IDs instead of Boomerang name? (
"1006-634-0171" instead of Bellen) I have tried it putting
=VLOOKUP(A15,VTable,4,0), but it resulted as "N/a" if I select the whole
table (VTable). But if I do it without "Boomerang Name Column that means
[=VLOOKUP(A15,B5:D12,3,0)], it gives right answer. Can you please tell me
what is wrong that I am doing while taking the whole table? 

Автор cosmel villalobos ( назад)
Man oh Man ! That was some class right there! thank a lot for your input .
I was able to use vlookup for a report I needed to do!!!

Автор keith noone (74 года назад)
Fantastic video. Thank you so much!!

Автор cree ( назад)
hi i downloaded the worksheet, I entered v lookup but the table array keep
indicating a formula error i downloaded excel 2013 i previously had excel
starter 2010 and thought it was not compatible now it still does'nt work
any advice

Автор Maria Chat ( назад)
Thank you ever so much!!!!

Автор maylyn castillo (670 лет назад)
thank you so much.it made me efficient with my work,..very helpful.

Автор Ahmed Z ( назад)
Thanks for such excellent tutorials!

Автор Farida Zahra ( назад)
+ExcelIsFun can you please put more videos thx again

Автор Farida Zahra ( назад)
excel is really fun thx 

Автор neo greateramir ( назад)
I wonder if you can help me please.
I have an excel workbook with just over two hundred sheets. Each sheet is a
patient with their various blood results. Unfortunately the order of blood
results is not the same in each sheet. For example; “liver enzyme” in one
sheet may be in column “D” and in another sheet it may be in column “K”.
Is there a formula that can find the column with the heading “liver enzyme”
and then look for the highest value in that column? And if there is such a
formula, can I apply it to the whole workbook rather than separately to
each sheet one-by-one?
I would be most obliged if you could help me with this matter
Best wishes

Автор Excel Commands Formulas ( назад)
Nice Videos

Автор mike jay perez (1606 лет назад)
is it possible to connect cell based from what i input? 

Автор Adam Abdullah (573 года назад)
Thank you so much , Very helpful channel ,a lot of effort for free, you
deserve more than hitting like , god bless you

Автор Quentin Daniels ( назад)
"Epic Video", I love it! Your passion for what you do is phenomenal!

Автор Shambhu Patil ( назад)


Автор sambofolife ( назад)
hey man I think you just helped me master excel. I love you man!!! lol

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

URL 
Ссылка 

Поиск Видео

Top Видео

Top 100 >>>

Видео

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