Excel Basics #23: VLOOKUP function formula

[jasminefan1]

thank u for this ... video.. it realLy helped me to be ready for our exam Tommorow.. ;)

[Sherlock69Holmes]

Hi Mike ,

Got a Scenario to use Vlookup to return Multiple Values.

Please suggest or any better way to do so.

[ExcelIsFun]

@Sherlock69Holmes , I do not understand your question. Can you re-state it?

[Sherlock69Holmes]

@ExcelIsFun

Thanks for Reply.got the related video i am looking for.

Excel Magic Trick 359: Part 2: Return Multiple Items From One Lookup Value for Column w Formula

[prabodhtirkey]

this video is awsum it helped in clearing my doubt...:-)

[90FT822]

thank you very much. it helped me much

[ExcelIsFun]

@90FT822 , you are welcome!

[sillyjojo6]

Very helpful, keep doing what you're doing!!!

[ExcelIsFun]

@sillyjojo6 , you are welcome!

[tuberubering]

Did you mean to say "Now, we can use a 1 for TRUE and a 0 for FALSE" ?

Or else, "Now, we can use a 1 for APPROXIMATE and a 0 FOR EXACT"?

You said "Now, we can use a 1 for TRUE and a FALSE for EXACT."

Thanks :)

[ExcelIsFun]

@tuberubering , Nice catch!!

It is: "Now, we can use a 1 for TRUE and a 0 for FALSE" and "Now, we can use a 1 for APPROXIMATE and a 0 FOR EXACT.

I love being on a good team!

[raghunandanchoudhary]

I Have gone through the entire series of ecle basics and i am sure this will do wonders for me !!!

I beleive its time for high line class now :)

[ExcelIsFun]

@raghunandanchoudhary , Yes, Highline next!!!

[raghunandanchoudhary]

truely Sir u really helped me a lot ... thanks a lot... cant describe in words :)

[ExcelIsFun]

@raghunandanchoudhary , you are welcome!

[waqas3131]

example

24.05*11

I want to multiply 0.05 with 11 but I want that automatically it should be work when I multiply to cells. what is condition for it sir?

[waqas3131]

Great work Sir,

Sir I have a question.

I want to multiply my answers in next row.

I can do so, but complication is that I want to multiply decimal numbers (after point numbers) can you please tell me how can I do that? English is not my home language, so sorry for mistakes if I did.

[learninghow2cook2011]

Thanks!....This is good stuff!

[ExcelIsFun]

You are welcome!

[rjulyanto]

thank you....thank you....thank you....

[ExcelIsFun]

You are welcome, welcome, welcome!!!

[malikshehryar100]

hey i saw many of ur videos and loved them all

most importantly i have learned a lot

so i had a question that i have been unable to resolve for quite a while

and that is that I DONT WANT A DROP DOWN LIST ,rather i wish if i could just type in and it would highlight (lookup) for a similar item

what i mean is that it could look up in a way like u look up a formula in the MS excel formula box. Now if i write s in the box it would give me all formulas starting with s ans so on... plz tell me how??

[ExcelIsFun]

I do not know how to do that, try:

mrexcel[dot]com/forum

[snodahsc]

OH MY GOD.

You did what my teacher and the bloody tuition teacher that spat when he talked my mom made go to FAILED to do immediately. NOT ANNOY ME and get me to understand VLOOKUP without making me feel like i have down's syndrome. THANK YOU.

[waqid20]

Thanks a lot. Its helps me a lot.

[ExcelIsFun]

You are welcome!

[marcoholic22]

thank you so much for these tutorials...i hope i can get a job soon! :)

[ExcelIsFun]

You are welcome!

[ExcelIsFun]

Did you find the Excel 2007 Excel Basics workbook? I think you had the 2003 one...

[marcoholic22]

@ExcelIsFun yep!..found it! thanks again.

[BlackCherrry]

THANK YOU!

that is all! :)

[ExcelIsFun]

You are welcome!

[tofu920]

thats amazing!!! thx for the uploading :)

btw, do you have any tutorial about VBA? if you have, can you give me a link? thx

[ExcelIsFun]

Glad it helped. No videos on VBA. Sorry.

[stalkingpanda05]

These videos have so far been my main/only tool for learning excel. Thanks a lot!--I will recommend the vids to others!

[ExcelIsFun]

You are welcome!

[Pavithra9181]

Hey

I am about to view your 23rd video of Excel Basics. All 23 videos are awesome. I just dont know what order to follow. I think next is highline excel . I also see short excel lessons, excel magic trick, beauty of excel etc. Please can you frame some order for all your videos . That would be a great help for all your viewers. I have started to love excel because of you...

[ExcelIsFun]

Serach for "excelisfun", then click on the Channel link, then watch the auto video that plays at the channel. That video shows what videos to watch and in what order.

[cassidine612]

You are the Da Bomb! You series is awesome. What a Gem of a find! Thank you!

[ExcelIsFun]

You are welcome!

[adiyagama]

This is a great series of video, thank you very much

[ExcelIsFun]

You are welcome!

[xhannahong]

Hi, thanks for the video. But my 2nd column is words instead of prices. What formula should I use then ? Because the method does not seem to be working :( Thanks!

[ExcelIsFun]

put a 0 (zero) in the 4th argument - as seen in video. If that does not work, that means something else is causing the problem.

[ExcelIsFun]

put a 0 (zero) in the 4th argument - as seen in video. If that does not work, that means something else is causing the problem.

[ExcelIsFun]

Or, there is no match. For example if you try to match "Rad" (lookup_value) with "Rad " (in 1st column of (lookup_table) you will not get a match becasue of the extra space. Maybe check for extra spaces...

[aamir9940]

god bless you 

[ExcelIsFun]

I am glad that you liked them!

[ydingdong]

Thanks for all the tips its help me a lot, How do i convert a figure to a word? like an example: I will enter on a sheet amount 100 that will translate or covert into word - One Hundred Only.... etc...... please help me.

waiting to post on youtube on this inquiry

[aamir9940]

@ydingdong

[ExcelIsFun]

Search for and watch this video to see how to do this:

Highline Excel Class 54: Excel VBA code From Internet

[ydingdong]

Thanks for all the tips its help me a lot, How do i convert a figure to a word? like an example: I will enter on a sheet amount 100 that will translate or covert into word - One Hundred Only.... etc...... please help me.

[ExcelIsFun]

Search for and watch this video to see how to do this:

Highline Excel Class 54: Excel VBA code From Internet

[utscsiva]

This is GREAT

Thank you very much

[ExcelIsFun]

You are welcome!

[bogdancmb22]

hello...i have a problem and hope u can help me. for example i have in a cell this text: I090100A, i use left, mid and right to separate them in diferent cells like this I0 90 10 0A ... I090100A is in cell A1, IO in cell B1, 90 in cell C1, 10 in cell D1, 0A in cell E1. In cell F1 i use this formula =B1 & " " & C1 & " " & D1 & " " & E1 and the rezult is I0 90 10 0A, but i want that 10 to be with bold and to have the font bigger than other characters. How can i do that?

[ExcelIsFun]

Try asking Excel questions at the best Excel question site around:

mrexcel[dot]com/forum

It is easier to post Excel solutions at this site (YouTube Comments don't allow us to do much)

[ExcelIsFun]

Try asking Excel questions at the best Excel question site around:

mrexcel[dot]com/forum

It is easier to post Excel solutions at this site (YouTube Comments don't allow us to do much)

[bogdancmb22]

@ExcelIsFun

thanks

[ExcelIsFun]

You are welcome!

[ObiWan346]

Can you explain the making of the absolute cell, F4? It puts dollar signs around them put does what?

[ExcelIsFun]

See these videos for the why:

Excel Magic Trick #397: VLOOKUP Copied Down a Column (VLOOKUP Basics)

Excel Basics #8: Cell References Relative & Absolute

Excel Basics #11: Spreadsheet Setup & Cell References

[ObiWan346]

I noticed you can click and drag too. Do you need to do some adjusting when you do this? With #'s some were a bit off

[ExcelIsFun]

I am not sure that I understand your question. Can you re-state your question?

[ObiWan346]

@ExcelIsFun I noticed that if you drag to the right for multiple column lookups, some numbers change re: calculations. Is clicking and dragging something you would reccomend for the lookup function?

[ExcelIsFun]

I am not sure that I understand your question.

[ObiWan346]

thanks for this, but what if you have 5 columns of name, address, phone, etc that you want to pull and show all, would you put 1-4 for index?

[ExcelIsFun]

You probably put 2 - 5. See this video for how to do it:

Highline Excel Class 07: VLOOKUP function formula 7 Examples

The example you wnat is at the 18 - 22 minute mark.

[redshoe02]

Hi Mike, I guess what I'm going to say next had been said by thousands of people who have truly benefited from your videos.

You are really contributing a great deed to the society, to those who are actively learning. This is by far the most comprehensive tutorial of all kinds that i have ever come across. GREAT WORK!

Btw, i have an inquiry, I have just finished watching the whole #23 series of excel basics, but there are thousand more excel tutorial on your link. What is your recommendations?

[ExcelIsFun]

Great Question! After watching the Excel Basics playlist series, you watch the Highline Excel Class playlist series (59 videos about 20 minutes each). Please search for and watch this video title that tells you how to use the excelisfun channel at YouTube:

excelisfun Search & Find Excel Videos, Playlists, Download Excel Workbooks

This video talks about which videos to watch and in which order.

[08LiLjosh]

What if the table array has a give like this: 90 above, 80-89.. and so on? How would it be using the vlookup?

[ExcelIsFun]

The fits column of the lookup table would list:

...

80

90

just put the lower end for each category in the first column sorted assending. This video shows how to do that.

Also this video:

Highline Excel Class 07: VLOOKUP function formula 7 Examples

[arghey]

can you teach me how to make a transmutation table in excel, so that I will not need to enter them manually. Thank you

[ExcelIsFun]

I do not know what a "transmutation " table is. Sorry.

[arghey]

@ExcelIsFun

example for a 35 item test the lets say 35 = 100, then 39 is 99..............

how will I do it if I put 35 on the first column then 100 will automatically appear on the next column........thanks... I am a teacher thats why......I really need help....to make work easier...

[ExcelIsFun]

I do not understand your question. Can you state 1) how the data is set up, 2) what you are trying to do, 3) what the end result is, 4) and an example?

[arghey]

@ExcelIsFun Hi there its ok now after watching thoroughly your video about the use of vlookup function, I figured it out friend, I tried it and it worked. Your video is really helpful......Thank you,

[ExcelIsFun]

EXCELlent!!!! I am happy to hear that the video is helpful!

[DK160e]

your video helped me to get a job. Thank you so much.

[ExcelIsFun]

You are welcome!

[mtadros86]

Can you add lookup values?

[ExcelIsFun]

Yes, but you need to tell me more about your 1) data set up, 2) what your goal is, 3) what end result is. There are a few different ways to do it depending on what your specifically are trying to do.

[ExcelIsFun]

Here are a few videos:

Excel Magic Trick 287: Lookup Adding - Array & Non-Array Formula

Excel Magic Trick 320: Lookup Adding: SUMPRODUCT & SUMIF

Excel Magic Trick 366: Lookup Adding For Accounting Estimate

Excel Magic Trick 287: Lookup Adding - Array & Non-Array Formula

[LawOfficeSolution]

Great video -- I must have read 20 "explanations" of vLookup before I could rea;;u wrap my head around it, whish I'd just seen this first!

[ExcelIsFun]

I am glad that the videos help!!

[MrHamtube]

Hello, still i didnot get , how to download the file: where can find your channel? and then what

thanks

Hamid

[ExcelIsFun]

Search for and watch this video which shows how to download:

excelisfun Search & Find Excel Videos, Playlists, Download Excel Workbooks

[aman1245singh]

Hello sir, I just finished with the basic 23 video series and the 12 examples of if function. What should be my approach after this, would you recommend watching the highline series first or going through the 2 part vlook up videos, or both the above simultaneously. Thanks in advance.

[ExcelIsFun]

Watch the Highline Excel Class next!

[laukuzita]

You are the best! Very powerful guide and amazing work you have put in it. Also you do have a great voice to make video tutorials - because sometimes my ears are bleeding when i watch other people doing tutorials.

Respect for all your work and thanks a lot, because you really helped me to solve many things in excel.

[ExcelIsFun]

Thank you so much! I am glad that the videos help!

[divyaca]

Hey ur tutorials are really helpful...i've learnt so much!!Thank you!!! Keep up the good job...Hopefully i'll get a job tomorrow!!!

[ExcelIsFun]

Excel can only help when you are searching for a job!

[achakran]

this is simply amazing..thnk u so much!!

[ExcelIsFun]

You are welcome!

[TheDarkemotion21]

Can I ask a question

What if you wanted to type in the Price to find the product and 2 products were $17. How would you set up a vlookup that could extract multiple data?

[ExcelIsFun]

It is quite complicted. However, I have many videos on this topic. Try these three for starters:

Excel Magic Trick 358: Part 1: Return Multiple Items From One One Lookup Value

Excel Magic Trick 359: Part 2: Return Multiple Items From One One Lookup Value

Excel Magic Trick 360: Part 3: Return Multiple Items From One Lookup Value

[erikmarius]

great vids! do you have any tips/video for analysing a price increase, szenarios, etc.? would appreciate it a lot! greets

[ExcelIsFun]

I do have many videos on these topics. Search for these titles:

Excel Magic Trick # 267: Percentage Change Formula & Chart

Highline Excel Class 22: Scenarios & Scenarios Report

There are many more too.

[melinarajoy]

Thank you for sharing this material. Your capability to explain is amazing, very clear and not boring at all. I love shortcuts, I use them a lot and I´ve learned many others here. I am ready for the next series. Thanks again.

[ExcelIsFun]

I am glad that the videos helped! The Next Series is:

Highline Excel Class (59 videos)

[MrLollipozzzzzz]

thank yoooooooooouuuuuuu!!!!!!!!!!

i <3 Bill jelen

[ExcelIsFun]

You are weclome!

[MrLollipozzzzzz]

thank yoooooooooouuuuuuu!!!!!!!!!!

i <3 excells fun

[coolestguo]

I've viewed your first series and it was fantastic! Thank you for being so professional and helpful!! Ready to enjoy other series now:)

[ExcelIsFun]

The next series to watch is the Highline Excel Class Series (59 videos). This series picks up where the Bacis Series leaves off.

[manukumar1000]

thank you sir. this was awesome. keep up the good work

[ExcelIsFun]

You are welcome!

[far559]

Dear Mr Excel,

your videos have been really helpfull.

i need to lookup up formula....

what i mean is i have 3 formulas....and they are accoring to package a package b package c....so i want to lookup a formula in a cell when i give any of the formulas.

can u plz help me?

[ExcelIsFun]

I do not understand your question

--excelisfun

[far559]

wat i mean is...can i lookup a function through this vlookup function rather then a value?

i have 3 packages, a,b and c. they all contain 10,15 and 20 nites. so i want to put a function which calculates the amount of the nites with respect to package a,b or c,

[ExcelIsFun]

The CHOOSE function is the lookup function to use when you are looking up different calculations or functions. See these videos:

Highline Excel Class 46: CHOOSE function 4 Examples

or

Excel Magic Trick 392: CHOOSE function Randomly Generate 1, 0 or Blank

[jiaxiaobo]

Dear Mr. Excel,

You've obviously been told you're awesome for doing this a million times before, but count me as a million and one. So, so, so generous of you. Thank you.

[ExcelIsFun]

You are welcome! I am glad that the videos help!

Mr Exel is Bill "Mr Excel" Jelen.

--excelisfun

[oprabhu123]

This is "THE BEST" online Coaching, I have ever learnt. Thanks a Bunch for all your videos. They are excellent, consistent, High Quality. I am High amazed. Thanks again. Definitely this has improved my working speed in office.

[ExcelIsFun]

You are welcome!!

[4enhoj4]

You, sir, are the man! Thanks for all the information! If there were educational olympics you would get the gold medal without a doubt!

[ExcelIsFun]

Thanks! I am glad that the videos are helpful!

[Witepapernitalli]

this video is west

[ExcelIsFun]

What does "west" mean?

[mumoy83]

good video for people who wants to learn more in excel...thanks!

[ExcelIsFun]

You are welcome!

[armaghanbhai]

Thanks very much man.. Very very very helpful dude.. worked for me.. amaizing.. I can't believe how useful youtube could be.. and the way of ur teaching is amaizing. I understand approx, 100%.

[ExcelIsFun]

You are welcome!

[Joannaxxxx]

oh my God - you are the manager I dont have and I can now refer to this any time I need reminding. Thank you so much. Where can I get more help?

BRILLIANT !

[ExcelIsFun]

You are welcome.

Watch this video title that shows how to find all the 900+ Excel videos at the "excelisfun" YouTube channel:

Search & Find Excel Videos, Playlists, Download Workbooks - excelisfun

[musicali78]

Thanks a lot "TUTOR MAN" you are a lot of help. It was so hard for me to understand this but you made it soooo easy!

[ExcelIsFun]

You are welcome!!

[samcookeethio]

Thanks very much for your time and effort. You are really one of the best instructors I have seen in my life. I am commited to mastering Excel and I am beginning to appreciate how applicable and powerful Excel is. I wish other capable and willing instructors follow your example and help students.

[ExcelIsFun]

I am glad that I could help. Having fun with Excel makes all the difference in the world!!

[jelu95]

Hi I know you have made many workbooks but I would please like to be directed to a particular function of VLOOKUP which involves having an item entered several times with different sets of values attached to it and having on a seperate worksheet the item lookibg up the total value of this item.

[ExcelIsFun]

If you know how to get to my Playlists, try the videos in this playlist:

Various Excel 2 Variable Lookup Videos

or this video title:

Various Excel Lookup Adding Tricks

if you don't know how to find playlists, search for and watch this video title:

Search For Excel Videos Download Excel Files excelisfun

[tmkm130]

I love these tutorials. All of the videos are so well thought out and presented perfectly. Thank you so much!

[ExcelIsFun]

Dear tmkm130,

I am glad that you like them!

--excelisfun

[randy0707186]

I am using the formula

=IF(C8=0,"",VLOOKUP(C8,F1:G20,­2,1)) but when I paste the formula down a row instead of excel just changing everything but my table location it is changing my table array as well. So if I were to paste this formula down one row it would look like this

=IF(C9=0,"",VLOOKUP(C9,F2:G21,­2,1))

instead of

=IF(C9=0,"",VLOOKUP(C9, F1:G20,2,1))

The table array should maintain the table location but it is changing for some reason. What do I need to do to stop this?

[ExcelIsFun]

Dear randy0707186,

Try:

=IF(C8=0,"",VLOOKUP(C8,$F$1:$G­$20, 2,1))

But you should go back and watch earlier videos in this series that teach about the 4 types of cell references. Without knowing about cell references you will not be able to do most intermediate and advanced Excel tricks!!

--excelisfun

[vanniesha1]

wow, your the best at excel.

[ExcelIsFun]

Dear vanniesha1,

Thank you for the compliment. But I am not the best at Excel! The people that hang out at the Mr Excel Message Board are the smartest Excel people that I know! I am just a guy having fun with Excel!

--excelisfun

[TheMrBenjamin]

Hey,

About time I thanked you for your tutorials. Looking for some Excel jobs and feel optimistic after watching your videos.

Thank You

[ExcelIsFun]

Dear TheMrBenjamin,

I am glad that the videos help!

--excelisfun

[ExcelIsFun]

Dear LELL27,

Do you know how to get tp the excelisfun Playlists?

If so, watch the Highline Excel Class Series. This covers many topics beyond the Excel Basics. The Highline Excel Class is not done yet, so over the nest month about15 to 20 more videos will be added.

If you need to find out where the Playlists are, watch this video: Search For Excel Videos Download Excel Files excelisfun

--excelisfun

[ExcelIsFun]

Dear LELL27,

When you get to the Playlists, they are numbered 01 - 11, you can watch them in that order to see all videos.

--excelisfun

[sermex2002]

Hey

Thanks for these grat video tutorials, I laready finished the first 23 and I have learned a lot of stuff, grat!!!

I have a little problem because my excell is in spanish and I have to figurade out the short cuts and functions.

Thanks for the videos!!!

[ExcelIsFun]

Dear sermex2002,

I am glad that the videos helped!

--excelisfun

[afganhuseynov]

thank you very much for your amazing work

[ExcelIsFun]

Dear4 afganhuseynov,

You are welcome!!

--excelisfun

[POPEWORSHIPER]

You get more from these videos than you do from CSUMB'S excel course. The lead instructor there for this course has no clue...but she's tenured so she's allowed to throw her students a textbook on day one and call it a semester.

[ExcelIsFun]

Dear POPEWORSHIPER,

I am glad that you find the videos useful!!

Hey, I am tenured, and I work non-stop every day teaching Excel, but that is only becasue it is so much fun!!!

--excelisfun

[POPEWORSHIPER]

I can tell you enjoy helping others with excel. Your explenations are unusually clear and helpfull. I had a college instructor who refused to help her students when they struggled with excel. She openly admitted to providing study guides that were only designed to help students achieve "c's" on tests, if they mastered the study guide. So I can hardly express with words how much I appreciate what you're doing.

[ExcelIsFun]

Dear POPEWORSHIPER,

Even more generally, our job in being human is to make the world a better place and help others!! That's it: that's #1. Also, life is like a boomerang, whatever you throw out, come back to you (in the long run).

It sounds like the teacher you are referring to is not trying her best to make the world a better place.

--excelisfun

[Baobao531]

wHAT CAN I SAY, YOU ARE SO SO SO SO SO AMAZING.

I SPENT 2 YEARS THINKING ABOUT DEVELOP MY EXCEL SKILLS, WITH YOUR CONTRIBUTION, I DID IT IN TWO DAYS.

MANY THANKS.

[ExcelIsFun]

Dear Baobao531,

EXCELlent!!! Just keep practicing to you will retain it!

--excelisfun

[Accountant2009ca]

I am really happy! I finished all 23 videos! Thank you Excelisfun. They have been extremly helpful. I am now going to download and read your textbook. I'm really interested in becoming an expert excel user. But first, I must master the basics :)

[ExcelIsFun]

Dear Accountant2009ca,

Since the vast majority of people do not have the Basics, once you get these, you will be very good with Excel!

After you read the book, be sure to watch the Lookup Series and the first few videos in the IF function Playlist, the SUMIF function Playlist and the COUNTIF function Playlist.

Once you become good with Excel, then you can help everyone in the office and become an Excel Superhero!!

--excelisfun

[bong252003]

I just finish watching the 23 basic videos of excel. What next is a must should I need to watch? Moreso, I am an accountant doing more on reconciliation of invoices between our records with our customers, any topic you suggest to speed my work? We usually download our data from our system as textfile and convert it to excel before we proceed reconciliation with customers. Please suggest.. Thanks..

[ExcelIsFun]

Dear bong252003,

Next you should watch this video that tells you how to use the excelisfun channel (things like searching for a video or playlist):

Search For Excel Videos, Playlists, Download Workbooks!

Here's a video on Reconciling:

Excel Magic Trick #31.5 IF, Data Validation, SUMIF

YouTubersLoveExcel#17: Hide Bank Rec Formula

Import Data video:

Excel Magic Trick #133: Import CSV Data

--excelisfun

[bong252003]

man, you are great,, even on this basic videos, i've learned so many things.. keep it up the good work man..

[ExcelIsFun]

Dear bong252003,

I am glad that the videos are useful!

Yes, most of us that learned Excel, are missing some of the basics and so this series usually has something for everyone!

--excelisfun

[quirky524]

Hi, After the last basic video, which is the video to follow to learn more?

Thanks for all the help.

Sujit, Mumbai

[ExcelIsFun]

Dear quirky524,

The trick is to watch the numbered Playlists in order. At least watch 01 - 05 (Basic Series, Lookup Series, Beauty of Excel, Names Series, Array Series).

Watch this video to learn how to use the excelisfun channel:

Search For Excel Videos, Playlists, Download Workbooks!

Oh ya, there are a few important Magic Tricks too:

Excel Magic Trick #203: SUMIF function formula 21 Examples

Excel Magic Trick #180: COUNTIF function formula 21 Examples

--excelisfun

[e30mmi]

thnx 4 info,,,

[ExcelIsFun]

Dear e30mmi,

I an glad that the videos are useful. If you like this one, try the Lookup Series Playlist or the VLOOKUP Series Playlist -- they many more lookup tricks!

--excelisfun