The ultimate cheat to VLOOKUP that anyone can use! No formulas required!
The ultimate cheat to VLOOKUP that anyone can use!
I am guessing that you have tried to use Vlookup before and always end up getting that ‘REF’, ‘#N/A’ or the vague ‘!ERROR’ error, right? Don’t feel bad, that stuff is tough. No, really. I am a season programmer and even I botch it up more often than I would like to admit!
If you feel overwhelmed, please don’t be. It is not just you! Don’t feel like it is. Vlookup can be stupidly difficult, for no real reason.
The other day my boss at my day job came up to me and asked me if I knew anything about Vlookup. I said that I did, and she beamed. She had been trying to learn Vlookup in Excel off-and-on for weeks and was getting nowhere but frustrated. She reached out to the I.T. guy at our facility and he pretty much said, “I duh-no?”.
I sat down at her desk, tried to explain Vlookup to her, but then stopped mid-sentence when I realized I was spewing out the same information that she had already reading online for the past week.
I smiled, and asked her point blank, “What is your end goal? What do you want to be able to do?” She told me that she wanted to pull data from one workbook, and put the result as a formula in a specific cell in another workbook, and auto fill everything else. That is when I closed my laptop, stood up and said, “Would it be okay if I came back in a week with an app that would do it for you?”
And that is exactly what I am going to be sharing with you today. Not only will I be breaking down the formulas I used when creating this addin, but I will also give you a free trial run with my app. Heck, its so useful that even I have been using this add-in for Excel at my own business.
How to create a Vlookup Formula, INDEX, MATCH, a ‘Master Of All’ and the drawbacks it holds…
As we mentioned, Vlookup can be quite confusing. Not only that, it’s not the only method that you need to learn if you want to use it effectively. In this section, I will be showing you the codes and links to other pages that explain Vlookup, though, chances are you have already seen them.
=VLOOKUP([SEARCH TERM],[TABLE TO LOOK IN AND RANGE], [COLUMN NUMBER],FALSE)
Did you know that Vlookup only works if the lookup value is the first column in the table your searching? Yep. If you want to, oh, I don’t know, have the lookup value be in the second column *gasp*, then you need to make a whole new table with that data in the first column. Either that, or you can learn two more functions called INDEX and MATCH.
=INDEX([SEARCH BOOK NAME]![RANGE TO RETURN],MATCH([SEARCH TERM], [SEARCH BOOK]![SEARCH RANGE],0))
So, yeah. Just for a simple ‘look this value up’, not only do you need to learn how to use Vlookup, but you also need to understand indexing numbers AND match functions. But, that’s not all, because the INDEX and MATCH are separate functions that combine together to create its own function! And, to make this even more confusing, you can combine that function with the already complicated Vlookup to make the ‘master of all lookup functions’ the Vlookup INDEX MATCHing function! You can read more about that here in the blog post ‘How to use VLOOKUP MATCH Combination in Excel’ over at ExcelChamps.com, but to be honest, at this point I think we have just stepped into the realm beyond bonkers…
Dear God, Make the Madness Stop!
Wow! Just what you wanted to hear, isn’t it? You want to learn one function, but now you need to know five separate ones just because your lookup value is in the second column and not the first. What a joyful day…
If all that was a little much, don’t feel bad, it was for me too. So much so that I decided to only do it once. The one time was when I wrote EZ VLookup, that way I would never have to manually do it again. And it’s not just me. EZ VLookup is so simple and easy to use that I am 100% guaranteeing anyone can use it and understand it right off the bat. If you click your mouse, you can use EZ VLookup to make Vlookups, and make them fast!
The One-Click Method for Vlookup WITHOUT Formulas!
As I mentioned above, I created EZ VLookup to be a simple point-and-click approach to Vlookup. Little did I know that it would turn into the multi-functional lookup bundle that it is.
My number one focus above all else was to make this add-in as simple and user friendly as possible. The main tool is the ability to input Vlookups, Index and Match functions with a mouse click. I tried to make this so effortless that anyone can do it. Just watch the clip below.
When you open up the VLookup Window, your ‘lookup value’ is automatically selected in blue, while your ‘lookup answer’ is selected in pink. It will automatically select these cells based on what cell you have selected when you open the window. Of course, you can change this by just selecting another cell and clicking ‘use selection’ or manually type in your desired cell. When a new cell is selected, that cell will become highlighted.
Once the window is opened, select your workbook, worksheet, table, the column your ‘search value’ is in and the column your ‘answer’ is in. A sample of your formula will be listed below the selection box. Click ‘Add Formula’ and your new Vlookup formula will be added into your ‘lookup answer’ cell you previously selected.
That’s it! Simple, right? This works for Vlookup, Index and Match, and EZ VLookup picks the best one of those formulas for you automatically. Best part, it is all formula based! You can drag-and-drop the produced formula down to autofill the rest of your cells!
Oh, and did I mention, it was completely multi-book compatible? The workbook location is saved in the formula, so as long as you still have access to the book you are pulling from (and it is in the same location), even if that book isn’t open, the formula will still work!
But wait! There’s More!
That’s right, infomercial time! Nah, I’m just kidding. There are several other features designed to save you time and money; a table creator, table jumper, multi-book searching, locking/hiding columns and cells, selection printing… Like I said, lots of fun stuff. The best way to learn about these features though is to try it out for yourself!
Did you say Free?
- The table creator and editor, locking/hiding cells and columns and selection printing in portrait mode are all 100% unlocked for free.
- The VLookup window is limited to the first five headers of each table. That means that any table that is 5 columns or shorter are open season!
- The last two are the table jumping and combining all data tables (yeah, didn’t mention that one above), those two are limited to only the current opened workbook.
But even with the low price of the full version, you will earn back your money and more due to the simple fact of how much time you will save! Think about how much your time is worth on an hourly basis, and then think about how many of those hours you have spent trying to learn Vlookup. I am pretty sure EZ VLookup has already paid for itself and you haven’t even tried it yet!
If you want to give EZ VLookup a spin, just head on over to our main page, or you can click the banner below. You are literally one click away from being a master of Vlookup in Excel, and you didn’t even have to learn any code!
Click here to see how much time you can save with EZ VLookup!
