Excel Lookup Series #11: INDEX & MATCH functions Two 2 Way Lookup

[DollRN4u2]

Thanks for your suggestion.

[DollRN4u2]

All of your videos are awesome and so are you. Anyone who can do this stuff is awesome to me. Now, how do I take your financial excel formulas and put them into my exercise program for my ole' seniors? I get a couple of steps and then I go from blank to nuts to wanting to throw the puter outta the window? ....Seriously, do you have a private website that I can go to and get help? I just don't get it after the if, vlookup, and match columns. HELP!!! PLEASE....

[ExcelIsFun]

@DollRN4u2 , I do not do consulting.

Try this alternative site that is set up specifically to ask Excel questions and have dialog:

mrexcel [dot] com/forum

(mrexcel site is not affiliate's with excelisfun at YouTube)

This site has many people who can help you get the solutions you want.

If you ask a carefully constructed question, you will get answers quickly

[cgeorge2k9]

excellent set of tutorials. crazy tutor

[romepivot]

Great video, I love it

[ExcelIsFun]

@romepivot , I am glad that you like it!

[Sherlock69Holmes]

Hello Mike ,

Applied the same logic , but turned dataset to table & created two lists for Column & Row Headers.

Below is my function

=INDEX(Table1[[2009]:[2012]],M­ATCH(I7,MonthNameList,0),MATCH­(I6,YearNameList,0))

---when selected Apr in i7 - MATCH(I7,MonthNameList,0) returns 4 But MATCH(I6,YearNameList,0) -returns #N/A

Can you please tell where did it went wrong ?

Note : Creted table , so lists will increase dynamically .

[Sherlock69Holmes]

@ExcelIsFun

Here MonthNameList - Jan - Dec YearNameList - 2009 -2012

i7 is where i select Month & i6 is where i select Year .

[ExcelIsFun]

@Sherlock69Holmes , I do not know. Post this:

Below is my function

=INDEX(Table1[[2009]:[2012]],M­­ATCH(I7,MonthNameList,0),MATC­H­(I6,YearNameList,0))

---when selected Apr in i7 - MATCH(I7,MonthNameList,0) returns 4 But MATCH(I6,YearNameList,0) -returns #N/A

Can you please tell where did it went wrong ?

to:

mrexcel [dot] com/forum

[LYLxd]

You make it so fun. Appreciate it, it's so fresh :)

[jefficy1]

you gotta love excel a lot :O 1668 videos!

[69sico96]

Thank you so much. Think i love you. I had been looking for something like this for a VBA assignment :)

[ExcelIsFun]

@69sico96 , I am glad that it helps!

But I do not have VBA videos...

[69sico96]

@ExcelIsFun No worries, the same concept you used here I applied in the VBA assignment.

[hapoelist]

Thank You So Much! Youre Awesome! its help me :))

[ExcelIsFun]

@hapoelist , you are welcome!

[gotoria1]

index and match by excel is fun

[sucamentoes]

Mate, you are a Bloody Awesome.. thanks so much for this video.

[ExcelIsFun]

You are welcome!

[richynero]

Can I just say that I love this guy? He makes Excel sound fun. I love excel, but I can never actually explain it to anyone else and make it sound like fun. Mr ExcelisFun, I salute you (and love you)!

[ExcelIsFun]

I am glad that the video is fun and useful!!! I love you too - and if I did not I would not be making all these videos and giving them away for free!!! Or maybe it is that I have to give the videos away because they are too much fun ;)

[swimlforever]

do u know that u are going to haven :)

[ExcelIsFun]

I am glad that you liked the video!

[1002vicky]

Hey,

I was just going through your index and match functions and found it simply great. I want to download the sheet. but i am unable to go to your download page. can u please guide me with this. URGENTLY.

[ExcelIsFun]

Here is the link (file is at bottom):

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

[1002vicky]

@1002vicky

Thanks Sir.

This was helpful

I am using excel 2007 and wanted to know more about data validation. I have my data in sheet1 and can i use in this as data validation in sheet 2. Also need to understand if condition 1 is valid , then can data be validated from 2nd column.

I hope i am clear.



[vinnyware1]

Hi ExcellsFun, Can you help me ??

=(I16+(I16*0.99)+(I16*0.99^2)+­(I16*0.99^3)+(I16*0.99^4))

is there an easier way to write the above formula? im trying to reduce a number 'I16' by 1% per year for 5 years then add all together.

Its not too bad for 5 years but i wish to do the same for 25 years.

thanks

[ExcelIsFun]

Try: I16 = number you want I17 = Discount = 0,01 I18 = Formula = =1-I17 I19 = Number of Periods = 5 Formula 1 => =I16*(1+I18+I18^2+I18^3+I18^4) Formula 2 => =SUM(I16*I18^{0,1,2,3,4}) Formula 3 => =SUMPRODUCT(I16*I18^(ROW(INDIR­ECT("1:"&I19))-1)) Formula 3 is best because then you can automatically change the years from 5 to 25 and the formula updates. You should post this questions to: mrexcel[dot]com/forum Because then I can give a more complete answer (YouTube not best place to talk).

[ExcelIsFun]

If you post to mrexcel please send me the link so I can reply.

[muiseec]

Fun and Tastic, thanks

[ExcelIsFun]

You are welcome!

[manmachine87]

Thanks for the great videos! This one was the one I need but I'm glad I watched all the previous videos as well. I especially like your sound effects, great work.

[ExcelIsFun]

I am glad that you like it!

[muhwaliullah]

great teaching.i really like it

[BeastMaj0r]

Sir - you are an absolute legend!

[ExcelIsFun]

Just a guy having fun with Excel! I am glad that the videos help!

[aswilldf]

love it

[ExcelIsFun]

I am glad that you like it!

[suchdol0217]

Hello ExcelFun,

This is great!!! thanks ,but I wonder if there is a "vice versa" version of index& match , means you upload new data into selected row and column cross cell on the table?

Thank you in advance

[ExcelIsFun]

Serach for and watch this video title:

excelisfun Search for Excel Videos, Playlists & Download Workbooks

Then look for the playlist about:

1) Index and Match tricks

2) Reverse Two Way Lookup

[lorand34]

I suggest to use a more simple colorless example. Some viewers may work in a spreadsheet that contains almost nothing but numbers They will miss the fancy and helping design and might get confused. :D

I think that your videos are much detailed help witch is rare really helpful. You also helped me a lot in understanding things. it is great to see so much enthusiasm.

[ExcelIsFun]

Color!?

Anyway, if you want more about INDEX and MATCH try these videos:

Highline Excel Class 45: INDEX function & MATCH function 12 Unusual Examples

[amycecillau]

i like excel to talk to me so that i dont fall asleep in front of numbers.. the machine sound very sexy.. six dollars.. haha.. can u teach me how to make excel talk to me?

[ExcelIsFun]

Watch this video title:

Excel Magic Trick #71: Excel Will Talk To You

[amycecillau]

that one is a very annoying one... 5555555...muahahaha.. i had a big laugh!

[000epali878]

thanx man u saved my life

[ExcelIsFun]

You are welcome!

[yummybecky]

I've been trying to use SUM, SUMIF, SUMPRODUCT, VLOOKUP, etc. and couldn't figure it out. I finally figured out I had to use a combination of INDEX and MATCH, but had no idea how to do it. Thanks for your video - very helpful!

[ExcelIsFun]

EXCELlent! I am glad that it helped!

[cindywuzh]

Thank you! I learn a bit of Excel everyday by watching the videos. I also like the broooooo sound effect...so the numbers are not borning at all.

[ExcelIsFun]

I am glad that the videos help!

[microsuez]

I've thinking about this Excel problem for a while & true enough, you give me an exact solution of what i'm looking for.

Thank you so much......

[ExcelIsFun]

Yes, the INDEX and MATCH functions can really do magic!

[83Percy]

Thanks Mike!

That work perfectly. I've got one more question though. I am currently working on a shipping cost calculator and for weights over 1000 kgs (all fixed rates in a table) the rate is the same as for 1000kgs but plus 30 USD for every 100kgs above 1000 kgs. I've tried figuring out a way to making the formula "future update freindly" but I can't seem to get it easy enough for someone not that into excel to update it. Du you have any suggestions? Thanks a bunch!

Regards

Percy

[ExcelIsFun]

Dear 83Percy ,

See if this video helps:

Excel Magic Trick # 164: CEILING function in Price Formula

--excelisfun

[83Percy]

Is there a way to add >1000 value in your income table that will work with match?

Thanks for a great and educational youtube channel!

[ExcelIsFun]

Dear 83Percy ,

If it is greater than or equal to 1000, then the formula in this video will work if:

1) We replace the last value in the row header (700) with 1000

2) Our Income in cell C18 is 1500 or some value greater than or equal to 1000

3) The last argument for the MATCH is 1

Then for any value greater than or equal to the 1000, the formula will return a value from the last row.

Let me know if that helps.

--excelisfun

[ShunmasBeeablo]

such a coll trick Mike and we can even hide the look up table. Cheers mate!

[ExcelIsFun]

Dear ShunmasBeeablo,

EXCELlent!!!

I am glad the trick is useful for you!

--excelisfun

[quirky524]

Only if I had known these functions before, I would have saved so much time. Thanks to you, I will be more efficient in future.

[ExcelIsFun]

Dear quirky524,

I am glad that the videos help!

--excelisfun

[sidharthoomman]

The concepts are crystal clear after seeing your videos. Thanks a lot for making it free and accessible to all of us.

[ExcelIsFun]

Dear sidharthoomman,

You are welcome!

Keep watching and rating! I'll Keep making videos.

--ExcelIsFun