Excel - OFFSET for a VLOOKUP Table: Episode 1619
Microsoft Excel Tutorial: Using OFFSET with VLOOKUP: Dynamic VLOOKUP Table for Multiple Companies.
Welcome to another episode of the MrExcel podcast. In today's episode, we will be discussing the use of OFFSET in conjunction with VLOOKUP. This question was sent in by Ron, who asked when it would be appropriate to use OFFSET with VLOOKUP. Well, I have one example that I have seen before, where we have multiple companies and we want to use a different VLOOKUP table based on which company is entered.
So, let's say we have company 100, company 200, and company 300, and we want to use a different VLOOKUP table depending on which company is entered. This is where the OFFSET function comes in handy. It allows us to define the start of the lookup table by specifying a starting cell, the number of rows down, and the number of columns to the right. The height and width of the range can also be defined, making the OFFSET function extremely flexible.
To demonstrate this, let's say we have a count of our cost centers in column A, and we want to use a different VLOOKUP table depending on which company is entered in column B. We can use the OFFSET function to define the lookup table by using the MATCH function to find the starting row and the COUNTIF function to determine the height of the range. This way, the lookup table will automatically adjust based on the company entered.
To put it all together, we can use the OFFSET function as the table range in our VLOOKUP formula. This allows us to create one super mega formula that will work for any company entered. Of course, this formula can get quite long and unwieldy, so it's always a good idea to build it in pieces and then put it all together at the end.
I hope this explanation has helped you understand the use of OFFSET for a VLOOKUP table. Thank you to Ron for sending in this question and thank you for tuning in to another episode of the MrExcel podcast. Don't forget to subscribe to our channel for more Excel tips and tricks. See you next time!
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
Table of Contents:
(00:00) OFFSET for VLOOKUP Table
(00:16) Explanation of using OFFSET for multiple companies
(00:30) Defining the start of the lookup table
(00:40) Flexibility of the OFFSET function
(01:00) Using MATCH to determine the starting row for the company
(01:13) Using COUNTIF to determine the number of rows for the company
(02:01) Building the OFFSET formula
(02:25) Copying the formula to use in VLOOKUP
(02:55) Testing the formula with different companies
(03:05) Combining the formula into one
(03:26) Explanation of building the formula in pieces
(03:36) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
This video answers these common search terms:
COUNTIF function in Excel
Creating a formula in Excel
Dynamic VLOOKUP table
Excel cost centers
Excel tips and tricks
MATCH function in Excel
Multiple companies in Excel
OFFSET function
Using OFFSET with VLOOKUP
VLOOKUP table
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Today's question comes from Ron and he would like to know, "when would I use OFFSET in conjunction with the VLOOKUP Function?" In Episode #1619, Bill "MrExcel" Jelen explains the use of OFFSET and VLOOKUP together as well as provides us a great example.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! http://www.mrexcel.com/learn2010/LE20...
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Смотрите видео Excel - OFFSET for a VLOOKUP Table: Episode 1619 онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь MrExcel.com 17 Декабрь 2012, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 15,290 раз и оно понравилось 77 людям.