Have you ever played Scrabble? It is the game where you receive points for constructing words from letters printed on small plastic tiles. In my opinion, the best tile to get is always the blank tile. It doesn’t score any points, but it can be used to represent any letter at all. Therefore, if you … Read more
Most Excel users think that VLOOKUP can only be used with a single criterion; until a few months ago I believed this was also the case. In the past I had used INDEX/MATCH or helper columns because I had not appreciated how much could be achieved with the VLOOKUP & CHOOSE combination. We have already … Read more
Unfortunately, VLOOKUP, whilst powerful, simple and easy to use, is one of the least flexible functions in Excel. Just imagine for a moment that we have entered the following formula into Cell E6 (see screenshot below). =VLOOKUP(E4,A2:B10,2,0) This formula is returning the value from the 2nd column (Column B) – result 21. But, what happens … Read more
Named Ranges are a very useful feature of Excel, but a lot of users do not know why or how to use them. This post is not about how to use Named Ranges, but specifically how to create, delete and manage Named Ranges automatically with VBA. Create Named Ranges Named Ranges can be created at … Read more
VLOOKUP is a very useful formula; most advanced Excel users still encounter situations where it would be a suitable solution. But a basic VLOOKUP has one major flaw; it cannot lookup a value to the left. The value being looked up must always be in the first column and the value returned must always be … Read more
I hardly ever use an approximate match with VLOOKUP due to the risk of getting an incorrect result. But I learned this little trick – which provided almost unbelievable speed increases. The problem With a VLOOKUP an exact match (i.e. including FALSE as the last argument) is a slow method of calculation. It will search … Read more
I encountered a situation recently where I needed to convert a text string into a formula for calculation. I was expecting to find Excel would have the ability to do this. And it does . . . in part. If you have a number stored as text you can multiply it by 1 and it … Read more
Today I would like to share with you the best tip I learned last year. Why has this had never occurred to me previously? I have no idea. But I can honestly say that this has already saved hours of my life. Given the long hours we are expected to work these days, I know … Read more
VLOOKUP is one of the most powerful functions in Excel. It allows you to look up a value from a table based on a search term. A simple example would be a where you had a long list of employees and their telephone numbers. You could use VLOOKUP to find the telephone number of any … Read more
A tolerance chart shows how a result compares to a maximum and minimum permitted range. These charts are often used in industry to check whether a process is working correctly and within permitted limits. The image below shows the type of chart we will be creating. The data This chart requires various values: Minimum value … Read more