The closest you'll get to an unbreakable Excel VLOOKUP formula. I'm going to give you five things you can do to a VLOOKUP formula that is going to make it completely bulletproof. By step 5 you can copy and paste the formula anywhere, insert columns, and even move your lookup table…all without needing to change the formula.FREE Shortcuts Cheat Sheet »» http://www.up4excel.co.uk/shortcuts
Starting with this formula:
»» =VLOOKUP(A2,Table!A1:C100,3,FALSE)
Use $ to fix cells
In step one we use the dollar signs to fix our range and cell references. We made sure that we can copy and paste this formula between different columns and rows and it will hold the table reference static, and make sure that we're always picking up the correct column.
»» =VLOOKUP($A2,Table!$A$1:$C$100,3,FALSE)Entire column ranges
Secondly we convert our ranges to use entire column ranges. The beauty of this is that we were not restricting the number of fields in our lookup table and it can grow to the full limit of Excel, which is well over a million entries.
»» =VLOOKUP($A2,Table!$A:$C,3,FALSE)Use MATCH
Thirdly we tackle the biggest issue within the VLOOKUP formula, the hard-coded number which is the column reference that we want to pick up. We make this dynamically lookup the actual column that we want from our lookup table and return its column number, therefore meaning that we can now finally rid ourselves of that hard-coded number. This allows us to copy and paste this formula around the spreadsheet into various different columns, picking up different columns from the lookup table, without having to change our formula at all.
»» VLOOKUP($A2,Table!$A:$C,MATCH(A$1,Table!$1:$1,0),FALSE)Use COLUMN
Next we added a belt and braces type measure. We actually decided that we wanted to be able to insert columns in front of our lookup table. This is particularly useful if you're giving a spreadsheet, with lookup formulas in, to another person, and you want them to be unrestricted in what they can do. If they start inserting columns left right and centre you don't want your formulas to mess up, so this will ensure that your lookup will always know where the first column of its lookup table is.
»» VLOOKUP($A2,Table!$A:$C,MATCH(A$1,Table!$1:$1,0)+COLUMN(Table!$A)-1,FALSE)Trap Errors (#N/A)
Finally we caught any errors by using the IFERROR formula, embedding our whole formula within that to return some useful information to the user or ourselves. I demonstrate how it could be used to identify errors and add an error counter or something like that.
»» =IFERROR(VLOOKUP($A2,Table!$A:$C,MATCH(A$1,Table!$1:$1,0)+COLUMN(Table!$A)-1,FALSE),“Error”)
Here at Up4Excel we’re on a mission to help YOU:
» Get your Excel skills UP and your task time DOWN
» Focus on shortcuts and fast impressive results
» Improve your productivity and free up your time
Everyone will assume you work 24 hours a day to produce the kind of output you’ll be producing in no time…. with the help of Up4Excel training.
Here’s some of the things you could do with the time you’re going to save:
» Invest in yourself, saving even more time with Up4Excel training videos
» Spend more of your time on interpreting rather than compiling reports
» Get ahead of the game and accelerate your next promotion
» Or perhaps leave work on time, or even early, and enjoy your home life
Our videos are packed full of ways to save time and impress those around you. Make sure you subscribe, and hit the bell icon, so you don’t miss out and fall behind!
» No More Rounding Errors: MS Excel ROUND Formula
======================================================================================
The Webcam I Use:
Logitech C920 HD Pro Webcam, Full HD 1080p
https://amzn.to/2nlc9FE
Recommended Advanced Formulas Book:
Ctrl+Shift+Enter: Mastering Excel Array Formulas (by Mike Girvin of ExcelIsFun fame)
======================================================================================
https://amzn.to/2ofPytU
Get more FREE Training on Up4Excel here: https://cutt.ly/ewsz9Sn
FREE Shortcuts Cheat Sheet »» http://www.up4excel.co.uk/shortcuts
======================================================================================
▶️ DTube
▶️ YouTube