Excel VLOOKUP with Dynamic Table using OFFSET function

25 Responses to “Excel VLOOKUP with Dynamic Table using OFFSET function”

1. ExcelIsFun on: 7 March 2010 at 10:23 am

   Excel Basics Playlist, then the Highline Excel Class playlist both show most of the trick is logical order!!!
2. ExcelIsFun on: 7 March 2010 at 10:59 am

   Wow! So you don’t know how to use the excelisfun channel yet! Search for and watch this video title:

   excelisfun Search for Excel Videos, Playlists & Download Workbooks

   This video shows everything. The best thing for the topics you listed is to watch the Excel Basics Playlist, then the Highline Excel Class playlist – then you can also find the PivotTable Playlist, Charts Playlist, etc.

   But take my advice, watch Excel Basics Playlist, then the Highline Excel Class playlist first.
3. emailuznow on: 7 March 2010 at 11:13 am

   Thanks Mike

   Which videos would you recommend to watch to learn about text strings, pivot tables, Charts and printing
4. ExcelIsFun on: 7 March 2010 at 11:54 am

   Yes – but COLUMN could cause problems if you insert new columns, whereas COLUMNS to increment a number would not cause problems if columns are inserted. Over the years I have tended to use MATCH in this situation more than often because it causes fewer problems and people tend to understand it more easily than COLUMNS to increment numbers.
5. emailuznow on: 7 March 2010 at 12:20 pm

   Mike, quick question

   Can you have used the columns function to retrieve the position instead of the match function?
6. ExcelIsFun on: 7 March 2010 at 12:41 pm

   With a PivotTable you still have to do something to create a dynamic source range. In this video, the OFFSET will do. In the next video #509 (the better option), you could use the Excel Table feature. Once you get the dynamic source range down, you could get records many ways: VLOOKUP, DGET, INDEX, or others.

   I dont know how to do it with GETPIVOTDATA function. How would you do it?
7. frankdcg1970 on: 7 March 2010 at 1:27 pm

   ok, a point of clarification — when have you found this to be better than setting up a pivottable and then using

   =getpivotdata(blahblahblah)

   thx in advance.
8. ExcelIsFun on: 7 March 2010 at 1:29 pm

   You are welcome!
9. MrMercuG on: 7 March 2010 at 1:56 pm

   Ok, thank you very much for your vids they really help
10. baalkta on: 7 March 2010 at 2:25 pm

    can u think of any excel project that includes most of the features that i mentioned in my previous comment??
11. ExcelIsFun on: 7 March 2010 at 2:48 pm

    I think the point of your teacher’s assignment is for you to find a problem and then solve it with those elements.

    You can serach the excelisfun channel for the various topics (VLOOKUP, INDEX, Data Validation, etc.) and then watch some videos to get some ideas.

    Watch this video to learn how to search excelisfun:

    excelisfun Search for Excel Videos, Playlists & Download Workbooks
12. baalkta on: 7 March 2010 at 3:15 pm

    hey my teacher wants me to do a project on excel that includes some important features such as if, data validation, arithmetic calculation (+,-,*,/), vlookup, match and index, iferror, linking data from 1 sheet to another, and also a user interface. can anyone suggest a simple project for me?
13. ExcelIsFun on: 7 March 2010 at 3:50 pm

    Also watch Excel Magic Trick 509 – because it shows an even easier way to do this.
14. ExcelIsFun on: 7 March 2010 at 4:26 pm

    Also watch Excel Magic Trick 509 – because it shows an even easier way to do this.
15. ExcelIsFun on: 7 March 2010 at 4:51 pm

    Also watch Excel Magic Trick 509 – because it shows an even easier way to do this.
16. ExcelIsFun on: 7 March 2010 at 5:16 pm

    Also watch Excel Magic Trick 509 – because it shows an even easier way to do this.
17. ExcelIsFun on: 7 March 2010 at 5:52 pm

    You are absolutely right. This video actually shows tricks that you can use to create a 3-D Relational Database in Excel!
18. ExcelIsFun on: 7 March 2010 at 6:25 pm

    There is a keyboard shortcut for todays date:

    Ctrl + ;

    I use this all the time and it is a big time saver!
19. andreso1 on: 7 March 2010 at 7:01 pm

    Just to add information, this table would work as a control sheet for my business.. Like, whenever I receive or spend money, i’d like to put in the sheet without worrying to put the dates, so that later I can analyze my weeks/months..
    thanks!
20. andreso1 on: 7 March 2010 at 7:08 pm

    Man, your videos are saving my life! thanks a lot…
    by the way, i don’t know if I didn’t pay the proper attention, but I’d like to know how to build a table that once I enter the data, it automatically puts the date in any cell.. can it be done by regular excel or I have to use VBA?
    I thought that using function TODAY() would work, but it turns out that the day after it’ll still be the function, and not only the value..
    thanks in advance!
    Cheers from Brazil
    Andre
21. ExcelIsFun on: 7 March 2010 at 7:31 pm

    I am glad that you like it.
22. kelch12 on: 7 March 2010 at 8:04 pm

    Another useful lesson. Thanks
23. ExcelIsFun on: 7 March 2010 at 8:52 pm

    EXCELlent!!!
24. ExcelIsFun on: 7 March 2010 at 9:06 pm

    You are welcome!
25. emailuznow on: 7 March 2010 at 9:14 pm

    Fab