# New XLOOKUP Function in Excel

### Part One

It’s finally here, well it is if you have the monthly update cycle of the subscription version of Excel.

# VLOOKUP and COLUMN Function Warning

### Be very careful using these two together

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.

# Handling Text and Real Numbers with VLOOKUP

### Helping you work with imported data

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.

# Towards a Shorter IF Function

### Returning a range

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.

# 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.

# 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.

# Avoiding Excel’s Blank look

### Using the ISBLANK function

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.

# Using wildcard characters in Excel functions

### Handling missing characters

Using wildcard characters allows you to create flexible calculations. When used with the SUMIF and SUMIFS functions you can include quite complex criteria.

# What to look out for in Excel’s VLOOKUP function

### An easy fix for a common problem

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.