Mr Excel & excelisfun Excel Trick #1: Two 2 Way Lookup

[Gbell3587]

awesome vid, i never knew how to use vb like user forms.....is there any other turotials on using vba user forms?

[ExcelIsFun]

@Gbell3587 , Search Bill Mr Excel Jelen YouTube site, he has a few videos about User Forms:

.youtube [dot] com/bjele123

[ssilentwish]

plz type you site address where from i can take that sheet and practice

[ExcelIsFun]

@ssilentwish , Here:

people.highline.edu/mgirvin/Ex­celIsFun.htm

[MrDevah]

You guys can control the government :P

[lera5266]

just love your videos...you have to open your own excel school.. you are such a great teacher!!!!!!!!!!!!

[ExcelIsFun]

@lera5266 , YouTube is the school! I am glad that you like the videos!

[doncarous]

hi mr excel,i am fresh graduate who is desirous of pursuing a career in finance,i am novice in excel and need your help.my email:carous_g2000@yahoo.com

[Lucream2]

Hi Mr Excel thanks for your videos there are all great. I have some questions for for you. Is it possible to make a pop up calendar in MS Excel 2007? If so could you make a video about this? Or do you have a DVD that I can purchase to learn how it is done?

[mos233389]

I had a similar problem to solve recently on a multiple foreign exchange rate conversion but couldn't get the match to work. I used a VLOOKUP with a MATCH embeded to find the respective FOREX rate by specifc date within a FOREX by month table. Thanks for the tips.

[vikramyt]

great video!

[ExcelIsFun]

You are welcome!

[SweetSeedTV]

One of the very best channels...Learn a ton from you guys. Thanks

[ExcelIsFun]

I am glad that the videos help!

[codebreaker2822]

I'm a very proficient user of Excel. Your instructional videos are nothing short of brilliant. What a joy to learn from you both, and Thank You!

[ExcelIsFun]

I am glad that you like the video!!!

[codebreaker2822]

I'm a very proficient user of Excel. What these guys are doing is nothing short of brilliant. Genius at work.

[1002vicky]

Hello Mr. excel.

I need to know how to convert numbers to dates again. eg. 40620 to be converted into date.

Also is there a sheet wherein i find solutions for all the formulas of excel 2007.

[ExcelIsFun]

For Number into date, click cell, Ctrl + 1, on Number tab apply Date Number format.

or

to convert number to date as text use this formula:

=TEXT(A1,"m/d/yy") where A1 has number.

[asimbaigb]

@1002vicky

ooh my GOD your exellent i like you both thanks keep it up by Pakistan

[minh108]

can you help if a person born on 14/3/1780 and dead on 12/3/1955. i tried to use the datedif function but return #value.

[ExcelIsFun]

I do not know how to do that. Post to THE best Excel question site:

mrexcel[dot]com/forum

One thing is that people can't live 250 years, and "14/3/1780 " is not a date.

[jetpaq]

Way off the subject for this video, but is there a way for VB from visual studio to use formated excel documents as variable sources? For instance if i have a bunch of info i want to import for use in a program in visual studio. Can i use excel to help populate that database?

[ExcelIsFun]

I do not know. There maybe a way, try:

mrexcel[dot]com/forum

THE best Excel question site.

[jetpaq]

I never get tired of seeing this kind of genius at work. When i win the lottery( I swear) I will buy you both a televsion and sattelite show. There needs to be a network that will put you guys on tv once a week! This seems simple, but the thought behind it was really awe inspiring! thanks so much.

[ExcelIsFun]

You are welcome!

[TheFfff10000]

thank you for your nice video relay very intrasting

[ExcelIsFun]

You are welcome!

[fjilfaz]

Thanks for sharing..you both are the best..

[ExcelIsFun]

I am glad that you like the video!

[garethgrandfather]

HI i need to Develop a formula that will make sure postcodes are in capital letters, have a gap in the middle, and cater for 6 or 7 character postcode formats so it looks like (NP13 3DY) insted of (np133dy) thanks

[ExcelIsFun]

Currently I am working 70 + hours a week...

Post question to THE best Excel question site:

mrexcel[dot]com/forum

[garethgrandfather]

HI i need to Develop a formula that will make sure postcodes are in capital letters, have a gap in the middle, and cater for 6 or 7 character postcode formats so it looks like (NP13 3DY) insted of (np133dy) thanks

[ExcelIsFun]

Currently I am working 70 + hours a week...

Post question to THE best Excel question site:

mrexcel[dot]com/forum

[OBLUKMT]

Hi can you help me ?

I need some formula a have some nubers in row

1,2,3,4,5,6,7,8,9,10

and i need some formula which will read and write allways the last 5 valuves from this row

so it will write 6,7,8,9,10

Can you help me please ?

[ExcelIsFun]

I sent e-mail to you.

[prectec]

Very clever, very professional but I hate using limited ranges

[ExcelIsFun]

If by limited range you mean that you would like a dynamic range, try these video titles:

Excel Dynamic Chart #10: OFFSET Function Dynamic Range

Excel Dynamic Chart #12: INDEX function as Alternative to OFFSET function for Dynamic Ranges

[vavuthy]

fun

[MeliJ07]

what if i have multiple criteria to match against - ex: one worksheet (in separate workbook) with 7 columns - need to compare 3 columns to determine what to 'carry' to second worksheet and input into a cell - one file is employee's deductions, other is insurance bill detail, need file to compare and select data in each - i have some vlookups and match's but it's not quite working - do i need nested if's?

[ExcelIsFun]

Try the best "ask Excel question" site around:

mrexcel..com/forum

That site is a better place to have a discussion about excel.

After you post send me the link (however many people will probably answer quickly at this site).

[RandyKelly19]

I tried the site you posted says it cannot be found. Is there a new site to download the books, Thanks for the videos.

[ExcelIsFun]

Search for

"excelisfun"

Go to the excelisfun channel at YouTube, then watch the intro video that automatically plays - it shows how to download,

[Omert83]

Hello guys! This is great. I have a question though.

I follow your process completely. I have one issue. Say I have the same data but for 10 different countries. Will this still work with the 3rd variable? If there is a simple answer to it then it will appreciate your response. If we will have to do something new for that then thats okay! :)

[ExcelIsFun]

by 3rd variable, do you mean there are dulplicates in 1st column or more than one table?

dulplicates in 1st column see this video title:

Excel Magic Trick # 159: VLOOKUP Duplicates 1st Column

more than one table see this video title:

Mr Excel & excelisfun Trick 40: VLOOKUP to 3 Different Lookup Tables (3 table_arrays)

[kayno9]

Thanks mike your so helpful. Keep up the good work. Your a genius of excel. We don't want to stop drinking from the "fountain of your excel knowledge"

[ExcelIsFun]

You are welcome!

Don't stop drinking!!

[kayno9]

Hi Mike,

Do you know why Mr Excel stopped making his own podcasts.

[ExcelIsFun]

I do not know why he stopped posted at iTunes. But if you look at his YouTube account, he has posted about 50 new videos in the last few weeks.

His YouTube id is:

bjele123

[BONESANDDONES]

I am new to excel 10 and have very minimal experience with 7. Do you have introductory tutorials that I could follow into the more difficult tricks. I want to learn this program, I just need to start from the get go! Thanks for your time and help with the tutorials, they are appreciated!

[ExcelIsFun]

Yes I have a series just on Excel Basics. Search for and watch this video title:

excelisfun Search for Excel Videos, Playlists & Download Workbooks

This will show how to find the playlists.

[62Dragonlady]

Hello,

I am currently making my own inventory workbook and your tutorials have been very helpful, i have a question though. I'm using a nested if formula in excel 2007 works really well. but i need it to check for the same information on two different worksheets how would i do this, do you already have a video I can watch?

[ExcelIsFun]

Try these videos:

Highline Excel Class 06: Worksheet & Workbook References

Excel Magic Trick 452: IF Function Formula 16 Examples

Excel Magic Trick 398: VLOOKUP with Two Lookup Values (IFERROR function also)

[nadzhabibi]

how to download this

[ExcelIsFun]

search for and watch this video title:

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

Also, if you watch the first 3 seconds of this video, I show the web address where you can download the workbooks.

[rinkuyadav19]

is there any way to download all of the video available at the youtube by just putting 1 single link download?

i mean just like that once i start the download & it stops when it download all the videos available here.

[ExcelIsFun]

no.

[ExcelIsFun]

There is not. You might try seraching Google for apps that might do it.

[rinkuyadav19]

realy good

u guys rock

:)

[ExcelIsFun]

You are welcome!

[tmkm130]

You guys are great! I love this stuff. I'm getting my "Excel Guru" shirt very soon!

[ExcelIsFun]

Cool! Excel Guru shirst are EXCELlent!

I just wish I had a shirt that says:

"Guy Having Fun With Excel!"

[abuli29]

This is great stuff guys. Awesome content, and even more awesome delivery!!

Thanks a lot

One Quick question: The link that comes in the beginning of the video doesnt seem to work... Any suggestions how to download the workbook?

[ExcelIsFun]

Search for and watch this video title:

Search For Excel Videos Download Excel Files excelisfun

This video that i made shows how to download workbooks.

[shanutube96]

Hey guys,

Great dedication.. Why are you doing all these and its free!

[ExcelIsFun]

Dear shanutube96,

Why? Because it is fun to help people!

--excelisfun

[kikoM27]

Great video u have there! :)

I would like to ask if there is a way to display the column and row header names in another cell based on the value in the table?

using the data set in the video..assuming i have the value "814" and i want to look up and display the respective column and row header names "Jun" and "Chevy" (which is based on 814) in another cell..how can i go about doing it?

e.g "814 cars of the Chevy model were sold in the month of Jun"

hope u can help..!

[ExcelIsFun]

Dear kikoM27,

Yes, but it is quite complicated. I have about 10 videos on this topic. Do you know how to find my Playlists? The videos are in the Playlists titled:

Various Excel INDEX w TRUE/FALSE criteria "Reverse Lookup"

If you don't know how to find the Playlists, search for and watch this video:

Search For Excel Videos Download Excel Files excelisfun

--excelisfun

[kikoM27]

Thanks!! :D i got what i needed from the Reverse 2-Way Lookup w Duplicates video..

[ExcelIsFun]

Dear kikoM27,

EXCELlent!!!!!

--excelisfun

[xeniakol]

you are so cool guys :-))

[ExcelIsFun]

Dear xeniakol,

Just guys having fun with excel!

--excelisfun

[pererachaminda]

a great video. i didnt know the 2nd method. i usually use either what Mr. Excel did or the SUMPRODUCT method.

[ExcelIsFun]

Dear perachaminda,

Most people use the INDEX MATCH method because the 2 functions together can provide answers to a broader range of Excel problems. The Intersector Operator is very obscure. Also, the INDIRECT function is a volatile function which means it recalculates every time Excel recalculates regardless of whether the precedents have changed; whereas most cells that have formulas with precedents that have not changed do not get recalculated (large spreadsheets this matters).

--excelisfun

[ExcelIsFun]

Dear perachaminda,

Becasue the INDEX and MATCH are such a powerful combo and because it solves many different problems, I have just added a Playlist at the excelisfun channel for INDEX & MATCH (26 videos).

--excelisfun

[pererachaminda]

Thanks excelisfun..

One quick question....when you have names that have more than one word (eg. Honda Civic), the list you create still has to include the defined names, not the ones you have in your table (Defined name = Honda_Civic). Is there any way to get the defined names directly from the table? (i had to use paste-names & then sort)

[ExcelIsFun]

Dear perachaminda,

Yes!!! I have a video that shows exactly how to do this:

YTL Excel #119: Amazing Lookup Solution!

Let me know if that works for you!

--excelisfun

--excelisfun

[pererachaminda]

Thank you for taking time to reply...

Yes, that video really does the trick. the strange problem i had was, the names i used had their words separated by hyphens, so when excel creates names it converts hyphens into em dashes (just like it happens when i type 2 hyphens & press Spacebar).

it seems like excel doesn't allow hyphens in "defined" names.

thanks again...

[pererachaminda]

i should say some names had hyphens some did not. so it is difficult to use SUBSTITUTE function.....

thanks

[pererachaminda]

Actually i was able to get it to work using SUBSTITUTE.

Thank you very much...........

[ExcelIsFun]

Dear pererachaminda,

No - sign in names! Have you seen my Names Video Series? It is amazing what you can do with names. Plus, if you download the workbook, the first sheet has the charatceters that are not allows in names.

Check it out!

--excelisfun