New XLOOKUP Function in Excel
It’s finally here, well it is if you have the monthly update cycle of the subscription version of Excel.
(more…)VLOOKUP and COLUMN Function Warning
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. (more…)
Handling Text and Real Numbers with VLOOKUP
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. (more…)
Towards a Shorter IF Function
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. (more…)
Adding a Percentage to a Subtotalled List
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. (more…)
Insert a Note in an Excel Formula
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. (more…)
Avoiding Excel’s Blank look
When building formulas you sometimes need to identify a blank cell. Let’s say you use a VLOOKUP function. If the cell you are looking up is blank it can cause the #N/A error.
(more…)Using wildcard characters in Excel functions
Using wildcard characters allows you to create flexible calculations. When used with the SUMIF and SUMIFS functions you can include quite complex criteria. (more…)
What to look out for in Excel’s VLOOKUP function
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. (more…)