Highline Excel Class 07: VLOOKUP function formula 7 Examples

[prudhvirajreddyable]

excellent sir excellent keep it up

[fbanjok]

Hi,

you are a star!! V lookups have always made a fool of me...but no more...you did that!! thanks again

[pjain23]

Hi Mike,

Your Excel videos are obviously amazing!

Will you also be doing MS Access , MS Project videos?

If not, do you know of any good resources to find the same?

Thanks!

[ExcelIsFun]

@pjain23 , I have basic videos on Windows Explorer, Word, PowerPoint, Excel, Access, Mail Merge:

youtube [dot] com/user/ExcelIsFun#grid/user/­B0DB785B9C9BE8A8

[miqbal7007]

Excellent sir,

you are doing awesome work

[ExcelIsFun]

@miqbal7007 , I am glad that you like them!

[Tote1883gas]

Many thanks, Best explanation yet subbed and added to favourites :)

[ExcelIsFun]

@Tote1883gas , I am glad that you found the video useful!

[rishahuj]

@Tote1883gas Have favorited it too!! Thanks bro for explaining it by breaking it down... this vlookup is crystal to me now...!!

[jwroyer]

love it. adding you to my favs.

[ExcelIsFun]

@jwroyer , Glad you like it!

[Ruma18]

It's very helpful thank you! <3

[ExcelIsFun]

@Ruma18 , you are welcome!

[barbdwyre]

Nicely explained.  Thank you!

[ExcelIsFun]

@barbdwyre , I am glad that it helped!

[bigsuperpower]

I been using excel a long time (before Google and youtube), and took years figure out just about the 1/8 of whats shown, great stuff..... nice method also...

[ExcelIsFun]

I am glad tha it helps!

[hetaotuziz]

really amazing excel tutorial..thanks so much.

[ExcelIsFun]

You are welcome!

[MariusChivu838814]

thanks man

[ExcelIsFun]

You are welcome!

[TilyMm]

wow!!!!! I´m spanish speaking but it´s very clear i love the video i´m going to take a look to every one

[ExcelIsFun]

I am glad that you like it!

[SuperMegamalik]

i love u fun excel u work and efforts are remarkable

[ExcelIsFun]

I am glad that you like it!

[valemilan1]

wow.. this video really help me .. i haven't got this formula before.. thx yooo.. hopefully this will help me to pass the test :D

[ExcelIsFun]

You are welcome! Good luck on the test!

[jaxterdel]

cool explanation!!

[ExcelIsFun]

I am glad that you liked it!!

[jaxterdel]

cool explanation..cheers!!

[badra818]

You are THE man!

[ExcelIsFun]

I am glad that you like the video!

[godfather2386]

Your videos are great.

Seriously, thank you so much for sharing the knowledge.

[ExcelIsFun]

You are welcome!

If you like the videos and you like to read my new book is "Slaying Excel Dragins" at Amazon for $19.

[VideoCesar07]

All I can say is "Wow!", and that is an understatement. I was having a miserable time trying to find a handful of accounts from a spreadsheet of over 50K. Trying to do a regular VLOOKUP with only a handful was not giving me the results I wanted until I ran into your tut after a google search. The part of selecting a cell range and giving it a name solved everything!. Thanks!

I'm gonna keep following your series from the start since your teaching method is very clear and well paced.

[ExcelIsFun]

I am glad that you like the video. The whole series will prepare you to know Excel well. You can also buy the new book I just published called "Slaying Excel Dragins" - 500 pages and about $19 at Amazon! The book covers almost everything in the Highline Series, but even in a more logical order for easy learning.

[VideoCesar07]

@ExcelIsFun: Thanks for the info on the book. I usually don't like it when people start pushing products on me but after seeing how easy you made Excel look I bought the book and have already put many of your tricks to good use after only having it for a day!!! This is definitely the best purchase I have made in a long time. Any chance you have or will make Access tutorials as well?

[ExcelIsFun]

Wow - I am honored tohear that from you!! I don't like having products pushed or me or even pushing products. The reason I am doing it is becasue the book got released in the middle of the book cycle (cuz of delays on my part ) :( , which means to book sellers the book does not exist. So the only way I can try to market it is with comments on my videos and stuff. Tell me what do you like about the book - what makes it a good purchase?

[ExcelIsFun]

I do have some Access Tutorials:

Office 2010 Class #42: Access Create Database…

Office 2010 Class #43: Access One To Many…

Office 2010 Class #44: Access Create Forms

Office 2010 Class #45: Creating Queries In Access

Office 2010 Class #46: Access Create Report…

Office 2010 Class #47: Export Data From Access To Excel…

Office 2010 Class #48: Import Excel Data Into Access…

Office 2010 Class #51: Word E-Mail Mail Merge…

[ExcelIsFun]

Another note: If you do like the book, it would help a small author like me if you would do a review at Amazon!!

[wickedmosher]

I have been using Excel for years and I never have used anything past a basic 1+1 formula, however, after watching these videos you have up, I have learned a lot and I am now improving my job big time. Thanks a million for posting these videos...

[ExcelIsFun]

I am glad that they help! If you watch the whole Highline Series and assimilate it, you will be an Excel Master! I also just published a book based on the Highline series entitled "Slaying Excel Dragons", 550 pages for $19.50 at Amazon!

[tomasek262]

WOW! Amazing :)

[ExcelIsFun]

I am glad that you like it!

[Edy11428]

I've learned a ton from watching your videos. You're a great teacher! keep it up!

[ExcelIsFun]

I am glad that they help!

[TheArtOfCallOfDuty]

Which video can I view to learn how to use "iserror" with a VLookup?  I'm new to this formula.

[ExcelIsFun]

Try this one:

Excel Magic Trick 333: #DIV/0! Error IF & ISERROR or IFERROR functions

[thisisouraccount]

u r the best teacher in the world

[ExcelIsFun]

I am glad that the video helped!

[chubely]

HELP please...

Say you had 3 columns instead of 2 and you wanted to do column 2 take away column 3 using vlookup on both of them, what would be the formulae?

Like for example

=vlookup(A1:C3,2,0) (Take away) Vlookup(A1:C3,3,0)

Formulae please...

[ExcelIsFun]

You almost had it!!

Try:

=vlookup(A1:C3,2,0) (Take away) Vlookup(A1:C3,3,0)

Just put minus sign where you have (take away).

[chubely]

@ExcelIsFun Thank you..like seriously =D ^_^

Just made a few silly mistakes in the formulae I suppose

[chubely]

@ExcelIsFun 1 more Q please

So my working formulae is

=IF(I2="Order",VLOOKUP(C2,'Mas­ter sheet'!B19:C23,2,0)-VLOOKUP(C2­,'Master sheet'!B19:D23,3,0),0)

But when I dragged it down, "B19:C23" changed to B20:C24 on the next row and it kept going like that as it went down. How can I make it stay as JUST B19:C23

[georgidelicious23]

You're very good! Thank you!!

[ExcelIsFun]

You are welcome!

[HammondBilingual]

thanks for making me look good at work!!!! haha

[HammondBilingual]

sweet vid!! i have a big drop down box with about 2600 names and unless i type in the exact name (last, first) it won'treturn any results. When i do fill in the name excatly it fill the info into a form i have set up in the same sheet.(im doing all this from vids i watched with you thanks!!!!) is there a way to have that drop down box give me suggestions? for instance if i want David Smith, whether i start with last or first name it wont matter, it will just give more suggestions as i type?

[ExcelIsFun]

You will probably need VBA for that.

Try the best "ask Excel question" site around:

mrexcel..com/forum

[LuvSpongy]

This is great and make me look good at work. why do you escape and F2 before you paste your formula?

[ExcelIsFun]

I am glad that it helps! Esc is to exit edit mopde and F2 is to put the formula in edit mode.

[gps0314]

what is the purpose of "ctrl + enter"?

[ExcelIsFun]

Here is a video that explains why:

Excel Magic Trick #208 Ctrl + Enter 6 Examples Of Efficiency

[Sideshowbob4100]

Awesome - thanks for improving my life :)

[ExcelIsFun]

You are welcome!

[juraxa]

I am wondering if you have similar classes for Access?

Thank you!!

[ExcelIsFun]

No. Sorry.

[juraxa]

I am a lithuanian, and I just recently found your lessons for Excel, and you can't imagine how it is helping me. Excel IS fun, it is such a huge discovery for me, thanks to you. And I find your lessons funny, you make it really easy and understandable, amazing job you've done.

I just want to ask, what encouraged you to take your time and create all these videos, I can't imagine how much time it took for you to do it.

Once again, thank you, you are big big help and great teacher :) Take care! :)

[ExcelIsFun]

I do it becasue our job on the planet earth is to make the world a better place.

[komudi]

Hi Thank you for your amazing video tutorials.

I am a big fan.

I have been trying to calculate dynamic commission :

eg: for first 1000 sales 5%. for sales between 1000 to 2000: 8% and so on.

The one explained in this video applies to the whole sale not a portion of it.

Is there a better formula for calculating the tier comission?

I have been doing it manually right now.

Would really appreciate if you could teach a better way.

Thank you

[ExcelIsFun]

Try these videos:

Excel Magic Trick 453: VLOOKUP for Commission Brackets Calculation (LOOKUP function also)

YouTubersLoveExcel#46: VLOOKUP & Previous Bracket Trick

[palak1582]

IS Data validation same as filters,or is there any other difference?

[ExcelIsFun]

They are different, Data Validation prevents bad data from going into a cell. Filter is an Excel feature that can filter a data set and hide rows that do not match criteria.

[emailuznow]

I want to be able to add records but have it update. I wanted to add another COLUMN F to input Addresses but i want the vlookup to update no matter how many records i enter by height or width

I hope this is more clear

[ExcelIsFun]

It is more clear. But tell me: names are the lookup_value, right?

[ExcelIsFun]

Yes, I just figured something out. I can make a vdieo next week. It involves 3 dynamic named ranges using the OFFSET function. One for the Data Validation. One for the VLOOKUP table and one for the MATCH function range. I also created a formula that picks up an new field names are created.

Cool question, emailuznow !!

[emailuznow]

Thank you for your response.

What i want to do is have a dynamic vlookup range so basically each time i enter some data i want it to update without having to use the LIST/TABLE feature.

e.g

In Column A - E i have some records

I have names in Col A

DOB's in col B

Tel num's in Col C

Email's in col D

and Nationalities in Col E

[emailuznow]

The columns function trick in vlookup is amazing. Would this work if you had a dynamic vlookup, i.e have records updating all the time, if yes how can i set up a dynamic vlookup and also have the columns function to return the lookup value. If you use the offset funtion, it works however if you skip a row (have the row blank) and then enter data in the row below, the vlookup and columns function does not work. Are there any videos on dynamic vlookup with the columns/row functions?

[ExcelIsFun]

I am not sure that I understand your question. If you want to add rows to a Vertical lookup table, you could you the List / Table feature that would create a dynamic range. As for a dynamic column number, it would be best to use a VLOOKUP & MATCH solution as seen in this video:

Excel Magic Trick #136: Two 2 way lookup with VLOOKUP & MATCH

If you explain your questions more clearly and give me a concrete example of what you are starting with and what the desired result is, I give better an

[ExcelIsFun]

If you explain your questions more clearly and give me a concrete example of what you are starting with and what the desired result is, I give better answer. The other option, since I am pretty slow and there are Excel people MUCH SMARTER than me, you can post questions at the Mr Excel Messaeg Board:

mrexcel[dot]com/forum.

I am continually amazed at what the people there can do!!!!

[farouk450]

Very Nice

Very Clear

Very helpful

Thanks thanks thanks

[ExcelIsFun]

You are welcome!

[CAMS2711]

Thanks, your videos helped me alot,they are very usefull,keep it up.

[ExcelIsFun]

You are welcome! Keep watching this Series for 52 more videos!

[miraclefriend]

i rely thank ful to you,i join as a financial analyst am after my MBA after few days came to know that am noting with out excel,but this videos make me the best...and big change in my carrier....tanking you

[ExcelIsFun]

You are welcome!

[GabiRav]

gel ,

many times we have tables from differents sources and the item we search for ( the first parameter in the LookUp ) is value or text or other.( does not the same as in the secound table ) usually I calculate the value , and after it I copy and past it ( spesial past ) . What is the best way to do it?

thank you for all your videos!!! ( amazing )

Gaby,

[ExcelIsFun]

One fast way to Paste Special Value:

Ctrl + C for the cell with the VLOOKUP result, then click in the cell where you want to Paste Special Values and:

Alt + E + S + V + Enter

another way:

Right-click key + S + V = Enter

[jotavich123]

I need help in one schedule, Can you do?

[ExcelIsFun]

I do not understand what you said.

[jaeheerang]

This is cool!

[ExcelIsFun]

Dear jaeheerang,

I am glad you like it! If you watch the whole series you will get a comprehensive view of how to use Excel.

--excelisfun

[chiranjeevigang12345]

i god good idea over excel upon watching this vedios...effort was great

[ExcelIsFun]

Dear chiranjeevigang12345,

I am glad that you got a good idea while watching!!

--excelisfun

[WillSin070685]

Oh Man! These are amazing!

Thanks so much for sharing with us!

[ExcelIsFun]

Dear WillSin070685,

You are welcome! i am happy to share!

--excelisfun

[kami645464]

You are absolutely right! These videos are simply amazing!

[Dave51262]

Tremendous! I love these videos! It is also funny when you say, "No way!"

[ExcelIsFun]

Dear Dave51262,

I say "No way' because it still amazes me how cool Excel is!

--excelisfun

[aznchiapet]

Excelisfun,

I think your videos are awesome! I have learned so much about Excel from just watching your videos. I especially appreciate the fact that you've made your videos available to the public. You are an amazing teacher! Thanks!

[ExcelIsFun]

Dear aznchiapet,

I am glad that you like the videos!

--excelisfun

[oburcrod]

Your site is addictive, I have been surfing through it all day long. In fact, by this time I had to halt because it is a lot of information (usefull tricks).

[ExcelIsFun]

Dear oburcrod,

I guess it would not be so addictive if Excel were not so much fun!!!

--excelisfun

[oburcrod]

Caramba! If you want to learn spanish or visite Mexicali, you have a house right here.

[ExcelIsFun]

Dear oburcrod,

Thanks!!!!

--excelisfun

[oburcrod]

God bless you for your loyality labor

[ExcelIsFun]

Dear oburcrod,

I am happy to make the videos (It's fun!)!

--excelisfun

[CircuitTheBand]

i was hinting that you suscribe back to me

[ExcelIsFun]

Dear CircuitTheBand,

OK. But you better make some great music videos!!!

--excelisfun

[CircuitTheBand]

this video rocks :).... i suscribed to you, you should return the favour ;)

[ExcelIsFun]

Dear CircuitTheBand,

I do not understand what you mean.

--excelisfun