It’s finally here, well it is if you have the monthly update cycle of the subscription version of Excel.
I saw a technique demonstrated recently with VLOOKUP that I hadn’t seen used before and thought at the time, that’s handy. Upon reflection however, I thought that’s a bit dangerous.
Sometimes data that comes into Excel with code numbers formatted as text. This can stop VLOOKUP functions from working and return the dreaded #N/A error. With a couple of tweaks you can lookup both real numbers and text numbers in the one formula.
Most people think that the IF function has to return a result. This leads to doing whole calculations in the true and false sections of the IF function. There is a way to create shorter functions.
Excel’s formulas are powerful. As an example we can create one formula that can be copied down to add a percentage calculation to a subtotalled list. This formula demonstrates a couple of useful techniques.
If you want to place a note inside an Excel formula you can use an old function that is, in most cases, redundant. The N function was used in early spreadsheets, but is hardly ever used in modern formula.
Using wildcard characters allows you to create flexible calculations. When used with the SUMIF and SUMIFS functions you can include quite complex criteria.
The VLOOKUP function is a popular method for extracting data from data lists. Its effectiveness depends on the quality of the data in the data list. I have had many questions from CPAs over the years asking why their VLOOKUP functions don’t work, when it all looks ok.