Excel - Dueling Excel - Dynamic Validation VLOOKUP - Episode 1379
2,277 views · Published 8 April 2011 · 8:49 · Indexed 14 November 2026
Channel: MrExcel.com · 2011 · Education
Microsoft Excel Tutorial: Dueling Excel - Dynamic Validation VLOOKUP. Welcome back to another episode of Dueling Excel, where Mike Girvin from Excel Is Fun and I, Bill Jelen from MrExcel, tackle tough Excel challenges. In this episode, we will be discussing dynamic validation and VLOOKUP, and how to use them together to create a more efficient and flexible spreadsheet. We start off by addressing a question from a viewer about using dynamic ranges for VLOOKUP and data validation in an invoice. Instead of hard coding the list for data validation and constantly updating the range for VLOOKUP, we show you how to use the table feature in Excel 2007 or newer. By creating a table and naming it, formulas that reference the table will automatically extend as the table grows. This saves time and effort in constantly updating ranges. Next, we dive into the specifics of using the table feature for both VLOOKUP and data validation. We show you how to use the INDIRECT function to refer to the table and the specific column within the table. This allows for a more dynamic and efficient way of referencing data, as the table will automatically update as new items are added. We also discuss the differences between using the table feature in Excel 2007 or newer versus Excel 2003. But, as we explore the table feature further, we discover a disconnect between data validation and VLOOKUP. While data validation automatically recognizes and updates the table nomenclature, VLOOKUP does not. We discuss the reasons behind this and how it may affect your use of the table feature in different areas of Excel. In the end, we come to the conclusion that while the table feature may not be supported in all areas of Excel, it is still a valuable tool for creating dynamic and efficient spreadsheets. We hope this episode has provided you with some useful tips and tricks for using the table feature in your own Excel projects. Thanks for tuning in and be sure to check out our other dueling podcasts for more Excel challenges and solutions. 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-on-youtube/ Table of Contents: (00:00) Dynamic Validation and VLOOKUP (00:14) Discussion on being sick while tackling a difficult topic (00:41) Dynamic ranges for VLOOKUP and Data Validation list for an invoice? (00:51) Using the table feature in Excel 2007 or newer (01:07) Benefits of using a table for dynamic ranges (01:17) Naming the table for easy reference (01:33) Using the table name in formulas for automatic extension (02:11) Benefits of using table formatting groups (02:21) Data validation using the INDIRECT formula (03:02) Advantages of using table nomenclature for data validation (03:57) Alternative method for dynamic ranges in Excel 2003 (04:07) Converting a range to a table and its effects on data validation and VLOOKUP (05:23) VLOOKUP not automatically updating with table nomenclature (06:00) Workaround for using table nomenclature in VLOOKUP (07:19) Discussion on Microsoft's decision to not support table nomenclature in certain features (08:39) 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: Chart engine Data Validation Dynamic ranges Excel 2003 Indirect formula Offset PivotTables Product table Table feature VLOOKUP Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153058/ Today's Dueling Podcast asks: "Can there be Dynamic Ranges for VLOOKUP and Data Validation List for an Invoice?" In Episode #1379, Mike "ExcelIsFun" Girvin and Bill "MrExcel" Jelen look at using Table Nomenclature in Validation and in VLOOKUP.
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920