Summary
Keywords
Full Transcript
Sign up for our Excel webinar, times added weekly: https://www.excelcampus.com/blueprint-registration/ In this video, I explain how to prevent errors in VLOOKUP formulas when inserting or deleting columns from the table array range. You can download the Excel file that I use in the video by going here: https://www.excelcampus.com/functions/vlookup-dynamic-column-index-number/ One common issue with VLOOKUP is that we can’t add or delete columns between the lookup column and the return column. The Column Index Number does not change automatically, and the formula will return the incorrect result. This can also result in the #REF! error if column index number is greater than the number of columns in the table array range. By using the COLUMNS function, you can make the Column Index Number dynamic, so that as you add or delete columns in your array, the Column Index Number changes and returns the correct result. You’ll see in the video that this solution also works when using Excel Tables. Leave a comment below if you have questions about how to use the COLUMNS function in your VLOOKUP formulas. Additional Resources: VLOOKUP Explained: https://www.youtube.com/watch?v=d3BYVQ6xIE4&t=338s Beginner’s Guide to Tables: https://www.youtube.com/watch?v=fJnJTff4Na8 VLOOKUP and Match: https://www.excelcampus.com/functions/vlookup-match-dynamic-duo/ For more Excel tips, talk, and techniques, please subscribe to our free weekly email newsletter: https://excelcampus.com/free #MsExcel #ExcelCampus #Vlookup 00:00 Introduction 01:06 The COLUMNS Function 02:03 Replacing the Column Index Number 03:43 Using the COLUMNS Function with Excel Tables
Continue this lesson in the app
Install CourseHive on Android or iOS to keep learning while you move.
