Excel Magic Trick 1231: VLOOKUP to 36 Different Tables: CHOOSE, OFFSET or INDIRECT function?

[@fernando5166]

Surprise the different solutions you teach us, thank you very much

[@excelisfun]

You are welcome very much!!!!

[@munirjamali1101]

Mike dear, there is nobody in the world who can teach Excel better than you; you simply rock.

[@excelisfun]

I am glad to help : )

[@munirjamali1101]

@excelisfun I'm unable to find your videos specifically teaching offset from basic to most advanced uses, similar to your videos on vlookup, index-match, or conditional formatting (with 50-something examples, my favourite).

[@excelisfun]

@@munirjamali1101 I do not have a video like that, but here are two other good uses for OFFSET: kzbin.info/www/bejne/ZqKwpISobN10jas kzbin.info/www/bejne/p5WYgJWta9qLbNU

[@ameyadalvi1129]

Simply amazing video. It has working files, description gives the time to jump to a particular topic, multiple ways shown in the video to obtain the same result, and every shortcut used is spoken out loud, shown onscreen and explained where necessary even if its the plain old ctrl c- ctrl v. Just amazing, most comprehensive video I have ever watched. Keep up the good work!

[@excelisfun]

Glad you like it! Thanks for clicking Thumbs Up!

[@Didda3]

Totally blown away. I absolutely cannot thank you enough. I am starting a new career in HR and this is just what I am looking for. I am currently looking for hidden magic formulas to make my work and that of my colleagues so much easier. I cannot wait to try this one. I did vlookup as vlookup today with some IF. Your presentation is professional and pure. Thank you friend. I shall be returning to learn more from you. :-)

[@wayneedmondson1065]

Hi Mike.. found this EMT at random on the sidebar of something else I was viewing. What a super set of tricks with CHOOSE, OFFSET and INDIRECT. I've not yet had the need to look up so many tables, but won't fear it in the future, now that I know three great ways to solve the problem. Thanks for sharing. Thumbs up!

[@excelisfun]

Yes, it sounds like you are having fun with Side Bar Videos. This was a great question that someone asked. You are welcome as always. i am happy to serve such a great Teammate as you, Sir!

[@amerfarooq1142]

Again I have found another Excellent video for the exactly solution I was looking for Thanks Mike, wish you a long & blessed life full of success

[@nadermounir8228]

Loving it. You are the best Mike. I think the choose function in here is the fastest

[@excelisfun]

: )

[@MrBerj1999]

love these vids, i find them really easy to follow and i love the 'here's a few options' approach...

[@excelisfun]

@plsp3c , Glad you like it!

[@SyedMuzammilMahasanShahi]

Wow. Again I have found this video exactly when I was looking for a solution of similar problem. Thanks Mike foe another EXCELlent video

[@excelisfun]

You are welcome!!! : )

[@RHH1095]

Great tutorial!!! Will make me a hero at work. --Note about your CountIf check-- At 13:28 during you last =CountIf check on your work, you will notice a #N/A is Row 1463. #N/As are "null" values and the =CountIf skips them. Therefore your zero(0) result will leave you short of 100% correct.

[@sachinrv1]

Hi there, the INDIRECT FUNCTION IS JUST AMAZING. its short and sweet, only at the beginning it is little complex to understand, but once you get a hang of it, it is so handy... thanks for sharing Sir...

[@sandeepsarkar2919]

You're my best mentor in excel... Sandeep

[@excelisfun]

Glad to help!! Thanks for your support : )

[@thebu1983]

This is one of the best tips I've learn so far! Thanks for sharing this knowledge!!!

[@excelisfun]

+Bruna Lima You are welcome!

[@MySpreadsheetLab]

3 great solutions Mike! Another idea would be a helper column that concatenates Table Name & Product and then a vlookup.

[@excelisfun]

Kevin Lehrbass The helper column and VLOOKUP is a great idea!

[@pmsocho]

Thanks for the solutions.

[@excelisfun]

+pmsocho You are welcome!

[@Bokery1000]

Great video! You really do wonders with Excel. It's amazing how much can be learnt from you.

[@excelisfun]

Glad the videos help!!! Thanks for the support with your comment, Thumbs Up and Sub : )

[@soloparentwanderer]

thank you thank you.. your the answer to the work am doing now

[@rajagopalang1990]

Just Superb Mike. Your explanation and the way you present your video is wonderful. You just cannot imagine how much I have gained from your videos. I need to ask you a question regarding excel tables. Could I ask here or it has to be asked separately? Thanks.

[@Luciano_mp]

Mike...você é o cara. Parabéns.

[@excelisfun]

Glad you like the video! Thanks for clicking Thumbs Up!

[@vijaysahal4556]

Superb 👍🏻👍🏻👍🏻👍🏻👍🏻

[@excelisfun]

Thanks 🤗

[@boracayrum]

Thank you very much Sir Mike.

[@mohamedsaid7514]

Thank you very much for your help

[@excelisfun]

You are welcome very much for the help! Thanks for the sup[port with your comment, Thumbs Up and Sub : )

[@StopWhining491]

Just WOW!

[@rajassy]

Thank you for your wonderful work!!

[@krn14242]

Wow. Brilliant. Thanks Mike.

[@excelisfun]

+krn14242 , You are welcome, WRH!!!!

[@excelisfun3903]

As of 8/25/2015 at 11:25 AM I updated the Excel files associated with this video. There was one Defined Name that had an incorrect defined range, so I fix it and uploaded the correct files!!!

[@excelisfun]

+excel is fun Cool!

[@dineshashara9391]

This was excellent sir

[@excelisfun]

Glad it was EXCELlent for you, dinesh!!!

[@thanasissy6953]

you're crazy man ! really helpful videos .

[@BillSzysz1]

Thanks for a great tips !!! If we have a Power Query then we can use it to do this - it will be solution by using UI only. Maybe tomorrow I will make a video about this :-)

[@excelisfun]

+Bill Szysz Cool! I can't wait!! :)

[@rexsung]

Thx a lot for the Great tips!! One question, what if I need to create more data for certain tables, will it extend the table range automatically? For instance, in table 1. West_Freestyle, there are 3 data in it, what if i set all formula already, then I would like to add more data in it.

[@excelisfun]

Rex Sung , it will not expand automatically. But... You could use the Excel Table feature for each range in CHOOSE or or Each Defined Name. I will have to make a video about that!!!!

[@rexsung]

+ExcelIsFun Thank you so much for the reply! Can't wait to see the new video. This formula you taught is really useful and related to my daily work, highly appreciate!!!

[@excelisfun]

+Rex Sung Glad it helps!

[@TheOndricek]

thank you!

[@tinashedzapasi5480]

Great Stuff

[@alandouglas2789]

I don't get why at 8:08 on OFFSET you left 'column' as blank? why does it use height and width? I know that is the table dimensions, but you would think that is what the 'column' part of the formula is for... VLOOKUP is so good, probably in my top 5 favourite formulas... INDEX is way better, except it pretty much requires MATCH or some other formula (or a reference to a cell that is a formula) to function.

[@MySpreadsheetLab]

Alan Douglas Mike could have put 0 for the column value of the offset function. Putting just the comma does the same thing...skips the argument as he's already in the correct column.

[@excelisfun]

Alan Douglas , the third argument asks how many columns to the left (negative number) or to the right (positive number) do you want to move from the starting cell (reference). We are not moving from the column so we put nothing in this argument.

[@alandouglas2789]

+Kevin Lehrbass Thanks for replying, I understand you could have put a '0' in or just skip it (you still need to put a comma in). I didn't know why he didn't he put a '1' in to offset it by 1 column extracting the answer *one column over* from his starting cell as he wanted the 'discount rate' I've never used this formula before, I'll look into it thanks

[@alandouglas2789]

+ExcelIsFun Thanks for the reply, I will look up some of your other videos on OFFSET to better my understanding, I've never used it before or even seen anyone use it...

[@excelisfun]

Alan Douglas Here is a playlist with videos to create dynamic ranges (there are some OFFSET examples in these): kzbin.info/aero/PLF6A29BD9E1CD5E6A

[@XLarium]

With only a few tables I prefer CHOOSE(), Otherwise I use INDIRECT().

[@excelisfun]

XLarium I think that is smart!

[@sandeepsarkar2919]

How to get your help ? Shall I sent you mail or else ? Please guide me.... Sandeep (Kolkata)

[@excelisfun]

The best place to have dialog to get Excel Solution is here: mrexcel.com/forum. There are 100s of Excel Masters waiting to answer : )

[@MySpreadsheetLab]

Here's my video using the helper column and vlookup approach: kzbin.info/www/bejne/aJuQdmyAodtofdk

[@excelisfun]

Kevin Lehrbass Thanks for the helper column solution!!!

[@excelisfun]

Kevin Lehrbass BTW, Thanks for finding the NA Error. I defined one of the Defined Names incorrectly... I will fix it and re-post the workbooks so our Teammate are helped! Thanks, Kevin!

[@MySpreadsheetLab]

ExcelIsFun Thanks to you Mike for all of the amazing videos!

[@excelisfun]

+Kevin Lehrbass I just uploaded the new files with the correct Defined Names so there will be no NA error!!!

[@Didda3]

I meant vlookup and hlookup sorry.

[@excelisfun]

As of 8/25/2015 at 11: 25 AM I updated the Excel files associated with this video. There was one Defined Name that had an incorrect defined range, so I fix it and uploaded the correct files!!!