Many of us have found VLOOKUPs to be extremely empowering
tools in Excel, not only meaning we don't need to repeat information
in our spreadsheets, but more importantly meaning that we only need to
maintain that data once.
Many of us have also banged our heads on our desks, walls, keyboards, and torn our hair out when trying to rationalise a VLOOKUP which doesn't find data that we can physically SEE is there ! The answer as to why a VLOOKUP doesn't work, with experience, becomes commonplace, but still tedious to find. There are a number of ways in which we can either change our data so that it can be found, or change the vlookup to add extra formula items which fix the formula.
Changing data isn't always desirable. If we load data in from the same source each time (e.g. another package, or cut and pasted from say, a mainframe), then the same maintenance is needed each time.Plus, the data might be needed in the original format somewhere else.
Changing the formula adds a bit of complexity we don't always want - you don't anlways want to understand new formula items every time you want to fix a VLOOKUP.
The first VLOOKUP Assistant was more of a VLOOKUP fixer, which provided a new VLKPFIX function that would try and solve all of your problems without asking you any questions. Unfortunately the breadth of problems with VLOOKUPs meant that this wasn't always possible. Also, and worse, the macro would only work on your PC, and any others that had th emacro installed - so you'd have an extra maintenance function with macros(probably worse than learning new Excel functions).
The final product tries to mix both solutions. No new macro entries are created, and whatever fix is suggested should work on any version of Excel, plus the Assistant will try and tell you how it has fixed the problem. If you don't understand the summary, then you can click on the text for a more detailed information web page like this one.
Sometimes the Assistant will offer to change the format of your data, usually offer a solution to prevent #N/A from appearing when a mismatch is valid, and generally show you how to add formula entries into your VLOOKUP to fix the problem. For example, if you're looking up a value that has a missing leading zero, then the assistant will tell you how to add it, and copy the new formula into the clipboard so you only have to paste the new formula over the old one. Understanding why the new formula entry works is useful, so the assistant is backed up by many web pages that explain how the new functions work (most Microsoft sites).
Typical problems fixed by the VLOOKUP Assistant ;
Searching in text for a numeric value (and vice versa)
Referencing a column outside the lookup range
Dynamic and Static Range Problems
Contrast of COUNTIF vs VLOOKUP
We hope you find the VLOOKUP Assistant useful, and value your comments. Over time, we will offer more and more add-ins which aim to improve your productivity and Excel understanding. Many of these will be free, and vastly improve your use of Excel.
Any comments warmly received at : add.innovation@f2s.com