Sign up to hear about free Excel training.
I won't share your email with anyone.
1 min read
In the image below we have a list of customers and a transfer date. Not all customers have a transfer date.
In the sales table below we need to bring in the transfer date from the lookup table. The problem is a standard XLOOKUP will return zero for the empty cells – see the entries 0/0/1900 below.
The solution is the LET function below.
The captures the XLOOKUP result in the x variable. It then tests x to see if it equals zero. It then displays a blank cell instead of a zero.
You could use just an IF function but repeating the XLOOKUP function makes the formula a lot longer – see below.
