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


I was busy preparing for a business trip last week and just didn’t like to post anything while I was away from home. I actually realized that posting from my desktop computer is the best because it’s very comfortable to type on a nice keyboard with a big screen.

Today, I would like to share with you a great video tutorial from ExcelIsFun on YouTube and explain why I did.

The reasons I shared the video are:

  • some advance Excel users may not come across real world situations that would involve them to use advanced/very complex lookups but it would be nice to keep in mind those techniques
  • yes it happens a lot that my counter colleagues in other departments have problems where one really can implement similar tricks to nicely solve them (not necessarily the ones I shared in this video)
  • OR advance Excel users with VBA knowledge tend to (sometimes) write a code to perform such lookups. Although it may be fun but if you are focusing on delivering an important output why would you waste some time when you can avoid the hassle of writing and debugging a code by simply using Excel built-in functions in some advance way
  • the tutorial refreshed my memory about the usage of CHOOSE (never used it), OFFSET (with height and weight), MATCH (with asterisk)
  • I thought it would have been nice if the tables can be named in batch instead of individually (but seems this can only be achieved by a macro/add-in) so that INDIRECT method (third green column in this tutorial) can be straightly applied
  • some of you might not know the little keyboard shortcut ( Ctrl + Backspace ) that lets you jump back to top after marking the end of a range (for e.g. in vlookup): watch 10 seconds starting from 10:00 minutes. I hope at least someone learns it too – like I did – from this blog ^_^

Excel is nice and fun. It’s a must in Finance world. I don’t see it as M$ thing >.<

Best of luck to all those who report numbers, graphs and reports

Leave a Reply